SQL按日期选择最新行 [英] SQL Select latest row by date
本文介绍了SQL按日期选择最新行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有大量数据每10分钟更新一次。
有128个唯一ID需要返回,但只有最新值
CURRENT CODE
SELECT DISTINCT
id,
MAX(extractdate)AS [extractdate],
total,
used,
free
FROM
maintable
INNER JOIN datatable ON maintainble.unkey = datatable.dataunkey
GROUP BY id,total,used,free
ORDER BY id
目前的输出
id extractdate累计使用的免费
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
等等
**希望输出**
id提取日期总计已用免费
1 2014-08-28 00:40: 00.000 50 10 40
2 2014-08-28 00:40:00.000 50 25 25
等等
SELECT
a。 id,
a.extractdate,
a.total,
a.used,
a.free
FROM(
SELECT
id,
MAX(extractdate)AS [extractdate],
total,
used,
free,
ROW_NUMBER()OVER(按ID分区ORDER BY MAX(extractdate)desc )AS rnk
FROM maintainble
INNER JOIN datatable ON maintainble.unkey = datatable.dataunkey
GROUP BY id,total,used,free)a
WHERE a.rnk = 1
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
这篇关于SQL按日期选择最新行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文