Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Analytics function query on 9.2.0.4
"James A. Williams" <jwilliam_at_aglresources.com> wrote in message
news:5003a2b9.0406181159.33a3154f_at_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.
James,
My suggestion is:
SELECT
*
FROM
( SELECT routeid, schedreaddate, mredate, timestamp, RANK() OVER ( PARTITION BY routeid ORDER BY timestamp DESC ) rn FROM ( SELECT routeid, schedreaddate, mredate, MAX( timestamp ) AS timestamp FROM route GROUP BY routeid, schedreaddate, mredate ) ) WHERE rn <= 3
The difference is that I have created another in-line view to retrieve the maximum value of the TIMESTAMP column.
BTW the name of the TIMESTAMP column conflicts with an Oracle datatype.
Douglas Hawthorne Received on Sat Jun 19 2004 - 01:35:08 CDT