CODEWITHSUNDEEP
mysql
prageeth
prageeth

Reputation: 7395

Get distinct results with latest records

I have following table in my database.

enter image description here

I am trying to write a query which returns some rows from the above table.

The returning list:

  1. Should not contain duplicates of Currency2_Id

  2. If there are duplicates of Currency2_Id found, the latest record should be returned

    For an example in above table there are two records which has Currency2_Id = 7. The latest record from those two is the record which has Date = 2012-12-28.
    So my final result should be something like this.

enter image description here

This is the query I tried.

SELECT Id, Currency1_Id, Rate, Currency2_Id, Date 
FROM currency_ex_rate_txn
GROUP BY Currency2_Id
ORDER BY Date DESC

But the resultset I get contains the record with Currency2_Id = 7 and Date = 2012-12-25 instead of the record with Currency2_Id = 7 and Date = 2012-12-28. Any help would be appreciated.

Upvotes: 1

Views: 99

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Try this:

SELECT 
  c1.Id, 
  c1.Currency1_Id, 
  c1.Rate, 
  c1.Currency2_Id, 
  c1.`Date`
FROM currency_ex_rate_txn c1
INNER JOIN
(
    SELECT Currency2_Id, MAX(`date`) LatestDate
    FROM currency_ex_rate_txn
    GROUP BY Currency2_Id
) c2  ON c1.Currency2_Id = c2.Currency2_Id
     AND c1.`date`       = c2.LatestDAte
ORDER BY c1.`Date` DESC;

Upvotes: 4

ethrbunny
ethrbunny

Reputation: 10469

 SELECT Id, Currency1_Id, Rate, Currency2_Id, Date   
      FROM currency_ex_rate_txn c1 
             where Date = (select max(Date) from currency_ex_rate_txn c2 
                           where c2.Currency1_Id = c1.Currency1_Id);

BTW - it's not a great idea to use reserved words as column names.

Upvotes: 2

Akhil
Akhil

Reputation: 2602

Hope this solves your problem
SELECT Id, Currency1_Id, Rate, Currency2_Id, DATE FROM currency_ex_rate_txn WHERE Id IN (SELECT MAX(ID) FROM currency_ex_rate_txn GROUP BY Currency1_Id)

Upvotes: 0

Related Questions