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" <[EMAIL PROTECTED]>
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: [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: Djordje Jankovic
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).
