SQL Select latest row by date

问题:

I have a large amount of data that updates every 10 minutes or so.

There are 128 unique ID's that need to be returned but with only there latest values

CURRENT CODE

SELECT DISTINCT 
id, 
MAX(extractdate) AS [extractdate],
total,
used,
free

FROM
maintable
INNER JOIN datatable ON maintable.unkey = datatable.dataunkey

GROUP BY id, total, used, free

ORDER BY id

CURRENT OUTPUT

id      extractdate                 total   used    free
1       2014-08-28 00:20:00.000     50      20      30
1       2014-08-28 00:30:00.000     50      30      20
1       2014-08-28 00:40:00.000     50      10      40
2       2014-08-28 00:20:00.000     50      20      30
2       2014-08-28 00:30:00.000     50      30      20
2       2014-08-28 00:40:00.000     50      25      25
etc etc

**DESIRED OUTPUT**

id      extractdate                 total   used    free
1       2014-08-28 00:40:00.000     50      10      40
2       2014-08-28 00:40:00.000     50      25      25
etc etc

回答1:

Try:

SELECT 
a.id,
a.extractdate,
a.total,
a.used,
a.free
FROM(
SELECT  
id, 
MAX(extractdate) AS [extractdate],
total,
used,
free,
ROW_NUMBER()OVER(partition by id ORDER BY MAX(extractdate) desc) AS rnk
FROM maintable
INNER JOIN datatable ON maintable.unkey = datatable.dataunkey
GROUP BY id, total, used, free )a
WHERE a.rnk = 1

回答2:

Should work, i've just tested it on the similar fall, only without join:

SELECT id, extractdate,total,used,free
FROM maintable m INNER JOIN datatable ON m.unkey = datatable.dataunkey
where extractdate = (select max(extractdate) from manitable m1 where m1.id = m.id) 
ORDER BY id