Mark,
You're on the right track thinking of the analytic functions (8.1.6 and
above), which among many things provide the ability to compare between rows
without having to do a self-join or multiple passes. Take a look at the lag
and lead functions.
Here's and example, making a single pass (plus the cost of the sort),
showing the use of the LAG function to get the status from the prior row:
SQL> l
1 select primarykey,
2 status,
3 dateinserted,
4 LAG(status,1) OVER (PARTITION BY primarykey
5 ORDER BY dateinserted) prior_status
6* from tricky
SQL> /
PRIMARYKEY S DATEINSER P
---------- - --------- -
1 A 01-JAN-03
1 A 02-JAN-03 A
1 C 03-JAN-03 A
1 B 04-JAN-03 C
1 B 05-JAN-03 B
1 B 06-JAN-03 B
1 D 07-JAN-03 B
1 D 08-JAN-03 D
1 E 09-JAN-03 D
1 E 10-JAN-03 E
1 E 11-JAN-03 E
2 A 01-JAN-03
2 A 02-JAN-03 A
2 B 03-JAN-03 A
2 B 04-JAN-03 B
2 C 05-JAN-03 B
And then it's a simple case of wrapping it in an in-line view and doing a
comparison.
SQL> Select *
2 From (select primarykey,
3 status,
4 dateinserted,
5 LAG(status,1) OVER (PARTITION BY primarykey
6 ORDER BY dateinserted) prior_status
7 from tricky) A
8 where status <> prior_status
9 /
PRIMARYKEY S DATEINSER P
---------- - --------- -
1 C 03-JAN-03 A
1 B 04-JAN-03 C
1 D 07-JAN-03 B
1 E 09-JAN-03 D
2 B 03-JAN-03 A
2 C 05-JAN-03 B
Note that I used the PARTITION BY since I was processing multiple keys (1
and 2). Also, if your requirement is such that the first occurrence of a key
should be reported, include "or prior_status is null" or use an NVL
function.
The elimination of the self join or multiple passes, or the elimination of
having to drop to cursor row at a time based processing to do the comparison
between rows, can result in a huge performance boost. For example, take
another common type of query when dealing with historical data:
1 select *
2 from tricky a
3 where dateinserted = (select max(dateinserted)
4 from tricky b
5* where a.primarykey = b.primarykey)
SQL> /
PRIMARYKEY S DATEINSER
---------- - ---------
1 E 11-JAN-03
2 C 05-JAN-03
With the use of analytic functions, ROW_NUMBER in this case, you can do
something like this:
1 Select a.primarykey, a.status, a.dateinserted
2 From (Select tricky.*, ROW_NUMBER () OVER (PARTITION BY primarykey
3 ORDER BY dateinserted desc)
rn
4 from tricky) a
5* Where rn=1
SQL> /
PRIMARYKEY S DATEINSER
---------- - ---------
1 E 11-JAN-03
2 C 05-JAN-03
And you have now eliminated the need for the sub-query and extra accesses on
the table. Anyway, sorry for the length, but sometimes examples help make
things clearer.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mark
> Richard
> Sent: Thursday, January 23, 2003 9:34 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Tricky query question
>
>
> 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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Larry Elkins
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).
