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
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