Search code examples
sqlsql-servert-sqlsubquery

Getting Group by right in SQL

I have a table which has data and history.

Date (d/m/y) fieldId (int) metaId (int) Value (Currency)
2/1/2020 1 3 1234
3/1/2020 1 3 1233
4/1/2020 1 3 1200
1/1/2020 2 3 1666
3/1/2020 2 3 1555

from the above table if I perform a query :

SELECT * FROM table WHERE fieldId IN( 1,2);

I should only get :

| 4/1/2020     | 1             | 3            | 1200             |
| 3/1/2020     | 2             | 3            | 1555             |

I know I will have to use a subquery to return only the latest data, but I am unable to work it out, Can someone help, please.

Solution

  • Using ROW_NUMBER() we can try:

    SELECT TOP 1 WITH TIES *
FROM yourTable
WHERE fieldId IN (1, 2)
ORDER BY ROW_NUMBER() OVER (PARTITION BY fieldId ORDER BY Date DESC);