PL or SQL? 1

[LarrySteele]

Sorry for the non-descriptive subject line, but couldn't figure out what to call this data extraction that I'm trying to accomplish - probably has a lot to do with my inability to Google an answer.

I'm wrestling with data from a source I cannot change. What I'm trying to extract is the first change occurrence for a given record id. Normally, I'd use a Min() function on the date, group by the id and the fluctuation field and I'd be off and running. However, this value field can go up and down and I want to trap the date when it changes, when the date it first holds a specific value (since that could happen multiple times).

It's much easier to show the data than to explain it...

Sample Input

[b]id        date            value[/b]
01        01/05/1998      3
01        08/03/1998      3
01        04/17/2002      4
01        09/01/2003      4
01        11/02/2005      4
01        06/18/2007      3
01        03/03/2009      3
02        04/09/2008      3
02        05/08/2009      3

Sample Output

[b]id        date            value[/b]
01        01/05/1998      3
01        04/17/2002      4
01        06/18/2007      3
02        04/09/2008      3

As you can see, id 01 has two records with a value of 3, which is what I want. Had I used a Min() function on the date and grouped by id and value, I would have missed the record of 06/18/2007.

Is there a relatively simple way to capture these changes in SQL or do I need to break out PL/SQL for this?

Walking through the table with PL/SQL is pretty straightforward. I'm just wondering if there's an SQL script that can capture this type of grouping.

For those strictly looking at PL/SQL for this, the pseudo-code is really easy:

cursor through table
if id or value changes
--record record
--save id and value in local variables
else
--loop to next row

 

[taupirho]

Given your inputs, here is one way.

1 select id,to_char(dte,'mm/dd/yyyy') dte,value from
2* tom
SQL> /

ID DTE VALUE
-- ---------- ----------
01 01/05/1998 3
01 08/03/1998 3
01 04/17/2002 4
01 09/01/2003 4
01 11/02/2005 4
01 06/18/2007 3
01 03/03/2009 3
02 04/09/2008 3
02 05/08/2009 3

9 rows selected.

SQL> select id,to_char(dte,'mm/dd/yyyy') dte,val from
2 (
3 select id,dte,value val,
4 lag(value) over(order by id,dte,value) lval,
5 lag(id) over(order by id,dte,value) lid
6 from tom
7 )
9 where lid <> id or lval <> val or lid is null
10 /

ID DTE VAL
-- ---------- ----------
01 01/05/1998 3
01 04/17/2002 4
01 06/18/2007 3
02 04/09/2008 3





In order to understand recursion, you must first understand recursion.

 

[LarrySteele]

Ah, the Lag() function! I do believe this is what I was looking for. I'll want to explore this function and its related function Lead().

Thanks much for pointing me in the right direction.