All Forums Database
standalone 17 posts Joined 08/08
24 Nov 2008
Select only when changing is occured

let say we have data like the one below:item|product_code|trx_dateA|001|2008-10- 10A|001|2008-10-11A|002|2008-10-12A|002|2008-10- 13A|001|2008-10-14A|001|2008-10-15A|003|2008-10-16and we want to select to produce only following records:item|product_code|trx_dateA|001|2008-10- 10A|002|2008-10-12A|001|2008-10-14A|003|2008-10-16can anyone help to find the correct query for that case?thanks.

Adeel Chaudhry 773 posts Joined 04/08
24 Nov 2008

Hello,Following may help you out:DROP TABLE Table1;CREATE VOLATILE TABLE Table1(Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 DATE) ON COMMIT PRESERVE ROWS;INSERT Table1 VALUES('A', '001', '2008-10-10');INSERT Table1 VALUES('A', '001', '2008-10-11');INSERT Table1 VALUES('A', '002', '2008-10-12');INSERT Table1 VALUES('A', '002', '2008-10-13');INSERT Table1 VALUES('A', '001', '2008-10-14');INSERT Table1 VALUES('A', '001', '2008-10-15');INSERT Table1 VALUES('A', '003', '2008-10-16');SELECT * FROM Table1 ORDER BY 3;SELECT Col1, Col2, Col3 FROM(SELECT Col1, Col2, Col3, RANK(Col3 ASC) MOD 2 AS RNK FROM Table1 QUALIFY(RNK = 1)) Alias1ORDER BY 3;Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

smilever 45 posts Joined 10/07
06 Jan 2009

Adeel,Could you explain how the Rank functions here in detailThanks

Adeel Chaudhry 773 posts Joined 04/08
06 Jan 2009

Following may explain it a bit more:DROP TABLE Table1;CREATE VOLATILE TABLE Table1(Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 DATE) ON COMMIT PRESERVE ROWS;INSERT Table1 VALUES('A', '001', '2008-10-10');INSERT Table1 VALUES('A', '001', '2008-10-11');INSERT Table1 VALUES('A', '002', '2008-10-12');INSERT Table1 VALUES('A', '002', '2008-10-13');INSERT Table1 VALUES('A', '001', '2008-10-14');INSERT Table1 VALUES('A', '001', '2008-10-15');INSERT Table1 VALUES('A', '003', '2008-10-16');SELECT * FROM Table1 ORDER BY 3;SELECT Col1, Col2, Col3, RANK(Col3 ASC) MOD 2 AS RNK FROM Table1;SELECT Col1, Col2, Col3, RANK(Col3 ASC) MOD 2 AS RNK FROM Table1 QUALIFY(RNK = 1);SELECT Col1, Col2, Col3 FROM(SELECT Col1, Col2, Col3, RANK(Col3 ASC) MOD 2 AS RNK FROM Table1 QUALIFY(RNK = 1)) Alias1ORDER BY 3;The logic is simple, to select dates which have even values for DAY.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.