怎么查询表中某一字段最大值所在行的数据
上一篇 /
下一篇 2007-03-01 16:29:47
/ 个人分类:oracle
自己想的:
SQL> select * from aaa;
ID SEQ_ID NAME
---------- ---------- --------------------
1 1 A
1 2 A
1 3 A
2 1 B
2 2 B
3 1 C
3 2 C
3 3 C
3 4 C
9 rows selected
SQL>
SQL> SELECT *
2 FROM AAA A
3 WHERE A.SEQ_ID = (SELECT MAX(B.SEQ_ID) FROM AAA B WHERE B.ID = A.ID)
4 /
ID SEQ_ID NAME
---------- ---------- --------------------
1 3 A
2 2 B
3 4 C
www.cnoug.org 论坛 yesl
TOP N中的TOP1,(依需要选用rank,dense_rank,row_number)
SELECT ID, SEQ_ID, NAME
FROM (SELECT ID,
SEQ_ID,
NAME,
RANK() OVER(PARTITION BY ID ORDER BY SEQ_ID DESC NULLS LAST) DRN
FROM TA)
WHERE DRN = 1
SQL> SELECT ID, SEQ_ID, NAME
2 FROM (SELECT ID,
3 SEQ_ID,
4 NAME,
5 RANK() OVER(PARTITION BY ID ORDER BY SEQ_ID DESC NULLS LAST) DRN
6 FROM AAA)
7 WHERE DRN =1
8 /
ID SEQ_ID NAME
---------- ---------- --------------------
1 3 A
2 2 B
3 4 C
相关阅读:
- 简明oracle8i培训手册 (adiao, 2006-12-08)
- Oracle中优化SQL的原则 (adiao, 2006-12-08)
- Oracle9i初始化参数中文说明 (adiao, 2006-12-08)
- Oracle的备份与恢复 (佳佳, 2006-12-10)
- 找回SYS密码 (adiao, 2006-12-11)
- Oracle SQL的优化 (佳佳, 2006-12-16)
- 数据完整性(转载) (selina_xz, 2006-12-16)
- oracle的学习心得一 (rphf66119, 2007-1-10)
- oracle学习二 (rphf66119, 2007-1-16)
- Oracle9i安装过程 (pele, 2007-1-23)
收藏
举报
TAG:
oracle