CODEWITHSUNDEEP
sqlsql-server
Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

Select multiple columns with only one distinct column in sql

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.

Upvotes: 5

Views: 11563

Answers (2)

Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

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

Upvotes: 2

Robert
Robert

Reputation: 25753

Try with GROUP BY clause and MIN function as below

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

Upvotes: 8

Related Questions