Re: Analytics function query on 9.2.0.4

"James A. Williams" <jwilliam_at_aglresources.com> wrote in message news:5003a2b9.0406211200.6a6c7b23_at_posting.google.com...
> Thanks Douglas!
> >
> > I knew I needed another inline view but I had a disconnect.
> >
> > BTW, this is vendor software that never thought Oracle would use the
> > TIMESTAMP datatype for a name now doubt in 9i. Makes DB2 to Oracle
> > conversions and vice-versa much easier.
>
> Thanks for the assistance over the weekend. I have been trying your
> suggestions with various results without having to create PL/SQL.
>
> I am limiting to 15 rows for display. My user wants to return the
> routeid with the highest timestamp during that monthl since the
> readings are done monthly even though a reread can occur as the below
> shows with carat. I need to return the route for each month based on
> the max timestamp. This gives them the most current read for the
> month based on timestamp without say installing a trigger.
>
> I would think I could do it in SQL.
>
>
> 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
> p4.route
>
> WHERE mredate IS NOT NULL
> GROUP BY
> routeid,
> schedreaddate,
> mredate
>
> )
> )
>
>
> WHERE rn <= 10
> AND rownum < 15
>
>
> 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
> 01011 20040521 20040524 5/25/2004 12:38:08 AM 1
> 01011 20040521 20040519 5/20/2004 12:34:36 AM 2 <
> 01011 20040422 20040421 4/21/2004 6:12:59 PM 3
> 01049 20040521 20040520 5/21/2004 12:31:58 AM 1
> 01049 20040422 20040423 4/26/2004 12:17:54 AM 2
> 01057 20040521 20040521 5/24/2004 12:32:30 AM 1
>
> I need the below. Note the 5/20 read read for 01011 routeid would be
> deleted.
>
> 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
> 01011 20040521 20040524 5/25/2004 12:38:08 AM 1
> 01011 20040422 20040421 4/21/2004 6:12:59 PM 3
> 01049 20040521 20040520 5/21/2004 12:31:58 AM 1
> 01049 20040422 20040423 4/26/2004 12:17:54 AM 2
> 01057 20040521 20040521 5/24/2004 12:32:30 AM 1

James,

Based on the data you have presented, you seem to be partitioning by routeid, and schedreaddate. If so, I make the following suggestion:

SELECT

      routeid,
      schedreaddate,
      mredate,
      timestamp
   FROM
      (
         SELECT
               routeid,
               schedreaddate,
               mredate,
               timestamp,
               RANK() OVER (
                  PARTITION BY routeid, schedreaddate
                  ORDER BY timestamp DESC
               ) rn
            FROM
               route
      )
   WHERE
      rn = 1

This appears to match your requirements.

Douglas Hawthorne Received on Tue Jun 22 2004 - 05:44:54 CDT