Geek Answers Handbook

Select multiple columns with one separate column in sql

I need to query the SQL to find all the different values ​​of one column, and I need an arbitrary value from two other columns. For example, consider the following table with three columns: CurrencyCode , BuyRate and SellRate :

 CurrencyCode BuyRate SellRate AUD 1.037 1.97 AUD 1.079 1.99 EUR 0.7288 0.8763 EUR 0.731 0.88 GBP 0.59 0.72

I want to get one line with a great CurrencyCode , possibly getting these three lines:

 CurrencyCode BuyRate SellRate AUD 1.037 1.97 EUR 0.7288 0.8763 GBP 0.59 0.72

I tried my SQL query, for example:

 SELECT distinct CurrencyCode, BuyRate, SellRate FROM Currencies

But I do not get the desired result, since it rounds all the columns.

+5
source share
2 answers

Try with the GROUP BY and MIN GROUP BY as shown below

 SELECT CurrencyCode, MIN(BuyRate), MIN(SellRate) FROM Currencies GROUP BY CurrencyCode
+6
source

So far I have found that this is the best answer to get Min(SellRate) based on Min(BuyRate)

eg.

  CurrencyCode BuyRate SellRate AUD 1.037 1.97 AUD 1.079 1.89 //Changed from 1.99 to 1.89 AUD 1.038 1.77 //New row added EUR 0.7288 0.8763 EUR 0.731 0.88 GBP 0.59 0.72

Here I expect AUD strings for BuyRate and SaleRate be 1.037 and 1.97

 select CurrencyCode, Min(BuyRate) as BuyRate, (select top 1 SellRate from Currencies as C where C.CurrencyCode=Currencies.CurrencyCode and C.BuyRate= Min(Currencies.BuyRate) order by SellRate) as SellRate from Currencies group by CurrencyCode
+2
source

More articles:


All Articles