Re: Analytics function query on 9.2.0.4

From: James Williams <willjamu_at_mindspring.com>
Date: Sat, 19 Jun 2004 15:31:18 GMT
Message-ID: <40d45bf6.1778653@news.east.earthlink.net>

>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
>

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. Received on Sat Jun 19 2004 - 10:31:18 CDT