Oracle-L: Re: Tricky query question

From: Djordje Jankovic <djordjej_at_rogers.com>
Date: Thu, 23 Jan 2003 21:05:17 -0800
Message-ID: <F001.0053946A.20030123210517@fatcity.com>

Is "primarykey" really a primary key for the table: you say "there might be 50 records for the primary key", or the table's PK is (primarykey,dateinserted) ?

If the primary key is not the table's PK, I guess a query along the following lines will give you what you want:

select t1.primarykey, t2.status, t2.dateinserted from (select primarykey, status, dateinserted from tab) t1

, (select primarykey, status, dateinserted from tab) t2

 where t1.primarykey = t2.primarykey
   and t1.dateinserted < t2.dateinserted
   and t1.dateinserted = (select max(dateinserted)
                                         from tab t3
                                        where t3.primarykey = t2.primarukey
                                             and t3.dateinserted <

t2.dateinserted)

and t1.status <> t2.status

Djordje

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, January 23, 2003 10:34 PM

> 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: Djordje Jankovic
  INET: djordjej_at_rogers.com

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

Received on Thu Jan 23 2003 - 23:05:17 CST