我想根据ID_DATE
以下记录中的列获取每个组中的最高值。通过...分组ID_TOPIC
CREATE TABLE DA_TBL(
DATA_ID VARCHAR2(50),
REF_DESC VARCHAR2(50),
DATE_L DATE NOT NULL,
ID_TOPIC VARCHAR2(50),
ID_DATE NUMBER
);
INSERT all
INTO DA_TBL VALUES ('1','sample 1',CURRENT_TIMESTAMP, 'local', 1)
INTO DA_TBL VALUES ('2','sample 2',CURRENT_TIMESTAMP, 'tradition', 2)
INTO DA_TBL VALUES ('3','sample 2',CURRENT_TIMESTAMP, 'gospel', 3)
INTO DA_TBL VALUES ('4','sample 4',CURRENT_TIMESTAMP, 'local', 4)
INTO DA_TBL VALUES ('5','sample 5',CURRENT_TIMESTAMP, 'gospel', 5)
INTO DA_TBL VALUES ('6','sample 6',CURRENT_TIMESTAMP, 'tradition', 6)
INTO DA_TBL VALUES ('7','sample 7',CURRENT_TIMESTAMP, 'gospel', 7)
INTO DA_TBL VALUES ('8','sample 8',CURRENT_TIMESTAMP, 'local', 8)
INTO DA_TBL VALUES ('9','sample 9',CURRENT_TIMESTAMP, 'tradition', 9)
INTO DA_TBL VALUES ('10','sample 10',CURRENT_TIMESTAMP, 'local', 10)
INTO DA_TBL VALUES ('11','sample 11',CURRENT_TIMESTAMP, 'gospel', 11)
SELECT * FROM dual;
我想要的是:
DATA_ID|REF_DESC |ID_TOPIC |ROWNUMBER|
-------|---------|---------|---------|
9 |sample 9 |tradition| 1|
10 |sample 10|local | 1|
11 |sample 11|gospel | 1|
我得到的是:
DATA_ID|REF_DESC|ID_TOPIC |ROWNUMBER|
-------|--------|---------|---------|
9 |sample 9|tradition| 1|
8 |sample 8|local | 1|
7 |sample 7|gospel | 1|
我尝试过的
SELECT *
FROM (SELECT DATA_ID, REF_DESC, ID_TOPIC
, ROW_NUMBER() OVER (PARTITION BY ID_TOPIC ORDER BY DATA_ID DESC) AS rownumber
FROM DA_TBL ORDER BY DATA_ID DESC)
WHERE rownumber = 1;
尝试以下查询:
SELECT
t1.DATA_ID,
t1.REF_DESC,
t1.ID_TOPIC
FROM yourTable t1
JOIN (
SELECT
ID_TOPIC,
MAX(ID_DATE) AS maxIdDate
FROM yourTable
GROUP BY ID_TOPIC
) t2 ON t1.ID_TOPIC = t2.ID_TOPIC AND t1.ID_DATE = t2.maxIdDate
我认为您在查询中使用了错误的列,因此如果以这种方式修复它,它也应该起作用:
SELECT
*
FROM (
SELECT
DATA_ID,
REF_DESC,
ID_TOPIC,
ROW_NUMBER() OVER (PARTITION BY ID_TOPIC ORDER BY ID_DATE DESC) AS rownumber
FROM DA_TBL
) t
WHERE rownumber = 1;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句