CODEWITHSUNDEEP
mysqlsqlcakephp-1.3greatest-n-per-group
Devang Rathod
Devang Rathod

Reputation: 6736

Group BY serial number and select latest (last) created date for that record

I have 1 table column id,serail_no,created.

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

Upvotes: 3

Views: 2398

Answers (2)

murali krishna
murali krishna

Reputation: 67

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`

Upvotes: -1

John Woo
John Woo

Reputation: 263693

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.

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

Upvotes: 3

Related Questions