Help on SQL

Database

Help on SQL

create table aa
(a int, b char);

insert into czhu.aa
values(1, 'a');
insert into czhu.aa
values(1, 'b');
insert into czhu.aa
values(2, 'c');
insert into czhu.aa
values(2, 'd');
insert into czhu.aa
values(2, 'e');
insert into czhu.aa
values(3, 'f');
insert into czhu.aa
values(1, 'g');
insert into czhu.aa
values(1, 'h');

Basically the data in table aa is like
1 A
1 B
2 C
2 D
2 E
3 F
1 G
1 H

What I want is a query to return the first change on column a order by column b:
1 A
2 C
3 F
1 G

(1, B) is not returned because from (1, A) to (1, B) there is no change in column a. And (A) is before (B). So only (1, A) is returned, not (1, B). Next one is (2, C). Since there is a change in column a from 1->2, so (2, C) is returned, and so on.

Is there an easy way to do that? I know I can use cursor and scan each row. What I am after is one SQL statement without using cursor.

Thanks
2 REPLIES

Re: Help on SQL

Thanks Dieter. It is really interesting.

I am trying to use the same technique to solve the below problem.

id data date flag

1 data 20060102 a
1 data 20060102 b
1 diff-data 20060103 b
1 diff-data 20060103 a
1 diff-data 20060104 b
1 diff-data 20060104 a
1 data 20060105 b
1 data 20060105 a

From the above set I need the following.

1 diff-data 20060104 a
1 data 20060105 a

Basicall I need all the 'a' records which are not equal to immediate 'b' before. Also I need to get the maximum(date wise) 'a' record if all the 'a' records are equal to the 'b' records.

Thanks for your help.
Jag
Junior Contributor

Re: Help on SQL

Hi Jag,
what's "immediate before"? Ordered by date?
Is (id,date,flag) unique?
Is Data a single column or multiple columns?

You just have to define how rows are grouped, this might produce the desired output:

SELECT id, data, date, flag
FROM
(
SELECT
id, data, date, flag,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY date, flag ) AS rnk1,
ROW_NUMBER() OVER (PARTITION BY id, dat ORDER BY date, flag ) AS rnk2,
rnk1-rnk2 AS grp --define a dummy group of similar columns using two different ranks
FROM dropme
QUALIFY grp <> 0 --remove the first group
AND flag = 'a'
) dt
QUALIFY RANK() OVER (PARTITION BY id, grp ORDER BY date DESC) = 1 --only the "last" occurrence
ORDER BY id, date, flag

If it's not the correct data, you have to supply some more information...

Dieter