All Forums Database
hotspear 2 posts Joined 12/04
30 Jun 2005
Help on SQL

create table aa(a int, b char);insert into czhu.aavalues(1, 'a');insert into czhu.aavalues(1, 'b');insert into czhu.aavalues(2, 'c');insert into czhu.aavalues(2, 'd');insert into czhu.aavalues(2, 'e');insert into czhu.aavalues(3, 'f');insert into czhu.aavalues(1, 'g');insert into czhu.aavalues(1, 'h');Basically the data in table aa is like1 A1 B2 C2 D2 E3 F1 G1 HWhat I want is a query to return the first change on column a order by column b:1 A2 C3 F1 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

cheruku 1 post Joined 10/06
05 Oct 2006

Thanks Dieter. It is really interesting.I am trying to use the same technique to solve the below problem.id data date flag1 data 20060102 a1 data 20060102 b1 diff-data 20060103 b1 diff-data 20060103 a1 diff-data 20060104 b1 diff-data 20060104 a1 data 20060105 b1 data 20060105 aFrom the above set I need the following.1 diff-data 20060104 a1 data 20060105 aBasicall 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

dnoeth 4628 posts Joined 11/04
10 Oct 2006

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, flagFROM ( 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' ) dtQUALIFY RANK() OVER (PARTITION BY id, grp ORDER BY date DESC) = 1 --only the "last" occurrenceORDER BY id, date, flagIf it's not the correct data, you have to supply some more information...Dieter

Dieter

You must sign in to leave a comment.