如何在SQL中按日期获取具有特定值的最新两行 [英] How to get latest two rows with certain value by date in SQL
问题描述
我的问题是,我有一些具有 varchar2
值和插入日期的表。
我想要做的是获得最新的两个这样的条目,这个条目按varchar2的值分组
是否可以包含一些 top(2)
而不是 max
在Oracle group by
?
编辑已更新为不计算同一个 varchar2
的重复日期值。
将 RANK()
替换为 DENSE_RANK()
,以便分配连续的等级,然后使用 distinct
以消除重复。
您可以使用 DENSE_RANK()
SELECT DISTINCT TXT,ENTRY_DATE
FROM(SELECT txt,
entry_date,
DENSE_RANK()OVER(PARTITION BY txt ORDER BY entry_date DESC)
AS myRank
FROM tmp_txt)Q1
WHERE Q1.MYRANK< 3
ORDER BY txt,entry_date DESC
输入:
txt | entry_date
xyz | 03/11/2014
xyz | 25/11/2014
abc | 19/11/2014
abc | 04/11/2014
xyz | 20/11/2014
abc | 02/11/2014
abc | 28/11/2014
xyz | 25/11/2014
abc | 28/11/2014
结果:
txt | entry_date
abc | 28/11/2014
abc | 19/11/2014
xyz | 25/11/2014
xyz | 20/11/2014
My question is that I have certain table with some varchar2
values and insert date.
What I want to do is to get latest two such entries grouped by this varchar2 value
Is it possible to include some top(2)
instead of max
in Oracle group by
?
EDIT Updated to not count duplicate date value for the same varchar2
.
Replaced RANK()
with DENSE_RANK()
such that it assigns consecutive ranks, then used distinct
to eliminate the duplicates.
You can use DENSE_RANK()
SELECT DISTINCT TXT, ENTRY_DATE
FROM (SELECT txt,
entry_date,
DENSE_RANK () OVER (PARTITION BY txt ORDER BY entry_date DESC)
AS myRank
FROM tmp_txt) Q1
WHERE Q1.MYRANK < 3
ORDER BY txt, entry_date DESC
Input:
txt | entry_date xyz | 03/11/2014 xyz | 25/11/2014 abc | 19/11/2014 abc | 04/11/2014 xyz | 20/11/2014 abc | 02/11/2014 abc | 28/11/2014 xyz | 25/11/2014 abc | 28/11/2014
Result:
txt | entry_date abc | 28/11/2014 abc | 19/11/2014 xyz | 25/11/2014 xyz | 20/11/2014
这篇关于如何在SQL中按日期获取具有特定值的最新两行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!