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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
