Serial number of the BY group and select the latest (latest) creation date for this entry
I have one table column ID, serail_no, created.
My display looks 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 the result to be like this.
3 1142B00072 2012-12-28 13:20:54
5 1142B11134 2013-01-16 16:42:34
+3
source to share
2 answers
you can use subquery to get the latest records for each serial_no
. The result of the subquery is then concatenated to the original table so that you can retrieve 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
+3
source to share
another solution: use the 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
source to share