0

I have a SQL Server table with the following data:

enter image description here

I need to get the latest records for each currency so the result should be:

enter image description here

Is it possible to get this result in just one select without doing nested selects?

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
VAAA
  • 14,531
  • 28
  • 130
  • 253
  • Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Tab Alleman Aug 15 '16 at 17:32
  • *without doing nested selects*: Why the restriction? – sstan Aug 15 '16 at 17:56
  • And another duplicate specific to SQL Server using the Row_Number function: http://stackoverflow.com/questions/4751913/retrieving-last-record-in-each-group-from-database-sql-server-2005-2008 – Tab Alleman Aug 15 '16 at 18:51

3 Answers3

1

You can use ORDER BY with TIES as follows:

SELECT TOP 1 WITH TIES *
FROM Src
ORDER BY ROW_NUMBER() OVER (PARTITION BY Currency ORDER BY ID DESC)
Paweł Dyl
  • 8,888
  • 1
  • 11
  • 27
0

You could use the window functions

;with cteBase as (
    Select *,RowNr=Row_Number() over(Partition By Currency Order By Date Desc) from YourTable
)
Select * from cteBase Where RowNr=1
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
-1

since your records are entered at the day of RATE update(seams that way), this should work... (please modify the limit as to match the number of currencies in your table)

SELECT ID,DISTINCT(CURRENCY),DATE,RATE FROM table ORDER BY CURRENCY,-DATE LIMIT 0,3

Dan Mihail Matei
  • 31
  • 2