查询分组中最新记录

查询人员表，按职位分组，取组内id最大的记录：
1.

SELECT * FROM person p where not exists (SELECT * FROM person where post_id = p.post_id and  id > p.id)

.


2.

select * from person where id in (SELECT max(id) FROM person group by post_id)

最简单的就是

SELECT max(id) FROM person group by post_id

再麻烦一点：

查询工具表中，所有分组中版本(version)最新的，并且是使用(state)中的工具：

1.

select * from tool t where state = 0 and not exists(select * from tool where state = 0 and name = t.name and version >t.version)

2.

select * from Tool where version in (SELECT max(version) FROM person where state = 0 group by name)

第二种方式严重不合理，当有相同版本时就不适用。建议使用第一种

有更好的方法的话希望可以分享。谢谢

评论

1 楼 armorking 2009-01-16  

在oracle下，可以用分析函数

SELECT 
    X.*
FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY POST_ID ORDER BY ID DESC) AS ROW_NO
        , A.*
    FROM PERSON A
) X
WHERE X.ROW_NO = 1