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

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