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