Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Analytics function query on 9.2.0.4

Analytics function query on 9.2.0.4

From: James A. Williams <jwilliam_at_aglresources.com>
Date: 18 Jun 2004 12:59:37 -0700
Message-ID: <5003a2b9.0406181159.33a3154f@posting.google.com>


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)  

where rn <= 3

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US