Select multiple columns with only one distinct column in sql

Question

I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another two 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 wish to retrieve one row with distinct CurrencyCode, perhaps getting these three rows:

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

I tried my SQL query like:

SELECT distinct CurrencyCode, BuyRate, SellRate FROM  Currencies

But I am not getting the desired result as it districts all the columns.

Which dbms product are you using?

jarlh
– jarlh

2015-05-22 09:20:52 +00:00
Commented May 22, 2015 at 9:20 — jarlh
– jarlh, Commented May 22, 2015 at 9:20

Robert · Accepted Answer · 2015-05-22 09:21:29Z

8

Try with GROUP BY clause and MIN function as below

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

answered May 22, 2015 at 9:21

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sabyasachi Mishra · Accepted Answer · 2015-07-21 10:57:50Z

Till now I found this is the best answer to get Min(SellRate) on the basis of 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 am expecting AUDrows for BuyRate and SaleRate will 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

Collectives™ on Stack Overflow

Select multiple columns with only one distinct column in sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related