Re: Analytics function query on 9.2.0.4

From: James A. Williams <jwilliam_at_aglresources.com>
Date: 21 Jun 2004 13:00:09 -0700
Message-ID: <5003a2b9.0406211200.6a6c7b23@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

Received on Mon Jun 21 2004 - 15:00:09 CDT