分组BY序列号并选择该记录的最新（最后）创建日期

I have 1 table column id,serail_no,created.

我有1个表列id，serail_no，已创建。

My display something like this

我的显示是这样的

id  serail_no    created

1   1142B00072   2012-11-05 11:36:00
2   1142B00072   2012-12-20 14:57:54 
3   1142B00072   2012-12-28 13:20:54

4   1142B11134   2012-11-25 14:57:54  
5   1142B11134   2013-01-16 16:42:34

So now i want output like this .

所以现在我想要这样的输出。

3   1142B00072   2012-12-28 13:20:54
5   1142B11134   2013-01-16 16:42:34

2 个解决方案

#1

you can use subquery to get the latest records for every serial_no. The result of the subquery is then join back on the original table so you can get the other columns.

您可以使用子查询来获取每个serial_no的最新记录。然后，子查询的结果将返回原始表，以便您可以获取其他列。

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT serial_no, MAX(created) max_date
            FROM    tableName
            GROUP BY serial_no
        ) b ON a.serial_no = b.serial_no AND
                a.created = b.max_date

SQLFiddle Demo
SQLFiddle演示

#2

-1

one more solution: Use rank function

还有一个解决方案：使用等级函数

select * from (with t as 
(select 1 id , '1142B00072'  as serail_no ,to_date('2012-11-05 11:36:00','yyyy-mm-dd hh24:mi:ss') created from dual
union all 
select 2  id , '1142B00072'   as serail_no ,to_date('2012-12-20 14:57:54' ,'yyyy-mm-dd hh24:mi:ss') created from dual
union all
select 3  id , '1142B00072'   as serail_no ,to_date('2012-12-28 13:20:54','yyyy-mm-dd hh24:mi:ss') created from dual
union all
select 4  id , '1142B11134'   as serail_no ,to_date('2012-11-25 14:57:54'  ,'yyyy-mm-dd hh24:mi:ss') created from dual
union all
select 5  id , '1142B11134'  as serail_no ,to_date('2013-01-16 16:42:34','yyyy-mm-dd hh24:mi:ss') created from dual)
select id,serail_no,created,rank() over ( partition by SERAIL_NO order by created desc ) rn from t)
where rn=1`

#1