Re: Tricky query question

Here's a trick for you. Let me know if it works.

select prev.dateinserted "date", prev.status "orig_status", curr.dateinserted "change_date", curr.status "new_status" from
(select rank() over (order by dateinserted asc) id, primarykey, status from table) curr,
(select rank() over (order by dateinserted asc) id, primarykey, status from table) prev
where curr.id-1 = prev.id
and curr.status <> prev.status;

--
Maria Aurora VT de la Vega
Oracle DBA
Philippine Stock Exchange, Inc.

"If you don't risk anything, you risk even more."
Mark Richard wrote:



> Hi All,

>

> This is a question for those who like writing tricky queries as a single

> SQL...

>

> Assume I have a table with the following structure (imaginary table to keep

> example simple):

>

> primarykey number(8)

> status char(1)

> dateinserted date

> <many other fields which are insignificant>

>

> A row is inserted into this table any time one, or more, fields change for

> the record.  What I'm trying to determine is the "dateinserted" for each

> time the value of "status" changed - remembering that there might be 50

> records for the primary key and five of those include a change in status -

> I want the dates of those five records (plus the value of status at each

> date).

>

> Is there any way to achieve this within a single SQL statement?  I realise

> that I could cursor through each record in date order and use a variable to

> store the previous value of status - but I'd love to compress this to a

> single statement.  I've seen the syntax "OVER" used in some queries and

> suspect this may be useful but I really don't understand it enough to know.

>

> Thanks in advance if you can help me on this one,

>

> Mark.

>

> PS: Sorry if this appears more than once - I've been getting mail server

> failures all day

>

> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

>    Privileged/Confidential information may be contained in this message.

>           If you are not the addressee indicated in this message

>        (or responsible for delivery of the message to such person),

>             you may not copy or deliver this message to anyone.

> In such case, you should destroy this message and kindly notify the sender

>            by reply e-mail or by telephone on (61 3) 9612-6999.

>    Please advise immediately if you or your employer does not consent to

>                 Internet e-mail for messages of this kind.

>         Opinions, conclusions and other information in this message

>               that do not relate to the official business of

>                          Transurban City Link Ltd

>          shall be understood as neither given nor endorsed by it.

> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

>

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> --

> Author: Mark Richard

>   INET: mrichard_at_transurban.com.au

>

> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> San Diego, California        -- Mailing list and web hosting services

> ---------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Maria Aurora VT de la Vega
  INET: mtdelavega_at_pse.org.ph

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).