Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Analytics function query on 9.2.0.4
I had a user who changed up a request on me for some SQL.
select * from
(select p4.ROUTEID,p4.SCHEDREADDATE,p4.MREDATE,p4.TIMESTAMP,
rank() over ( partition by p4.routeid order by p4.timestamp DESC) rn from p4.route p4)
Originally got me what I thought was wanted.
ROUTEID SCHEDREADDATE MREDATE TIMESTAMP RN
01003,20040521,20040519,5/20/2004 12:34:35 AM,1 01003,20040422,20040420,4/21/2004 12:05:19 AM,2 01005,20040521,20040519,5/20/2004 12:34:35 AM,1 01005,20040422,20040421,4/22/2004 12:06:06 AM,2 01007,20040521,20040519,5/20/2004 12:34:36 AM,1 01007,20040422,20040422,4/23/2004 12:05:10 AM,2 01009,20040521,20040521,5/24/2004 12:32:30 AM,1 01009,20040521,20040521,5/21/2004 6:26:57 PM,2 01009,20040422,20040422,4/23/2004 12:05:10 AM,3
Turns they want something that looks like the below
ROUTEID SCHEDREADDATE MREDATE TIMESTAMP RN
01003,20040521,20040519,5/20/2004 12:34:35 AM,1 01003,20040422,20040420,4/21/2004 12:05:19 AM,2 01005,20040521,20040519,5/20/2004 12:34:35 AM,1 01005,20040422,20040421,4/22/2004 12:06:06 AM,2 01007,20040521,20040519,5/20/2004 12:34:36 AM,1 01007,20040422,20040422,4/23/2004 12:05:10 AM,2 01009,20040521,20040521,5/24/2004 12:32:30 AM,1 01009,20040422,20040422,4/23/2004 12:05:10 AM,2
The 01009 routeid with the duplicate mredate and schedreaddate are removed based on the one with the greatest timestamp.
Based on the mredate and schereaddate being the same when the routeid is duplicated they wish to pull the row with the highest timestamp into the report.
Should be easy but I am missing it at the moment. Received on Fri Jun 18 2004 - 14:59:37 CDT