Select only when changing is occured

Database
Enthusiast

Select only when changing is occured

let say we have data like the one below:

item|product_code|trx_date
A|001|2008-10-10
A|001|2008-10-11
A|002|2008-10-12
A|002|2008-10-13
A|001|2008-10-14
A|001|2008-10-15
A|003|2008-10-16

and we want to select to produce only following records:
item|product_code|trx_date
A|001|2008-10-10
A|002|2008-10-12
A|001|2008-10-14
A|003|2008-10-16

can anyone help to find the correct query for that case?
thanks.
3 REPLIES
Teradata Employee

Re: Select only when changing is occured

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)
) Alias1
ORDER BY 3;

Regards,

Adeel
Enthusiast

Re: Select only when changing is occured

Adeel,

Could you explain how the Rank functions here in detail

Thanks
Teradata Employee

Re: Select only when changing is occured

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)
) Alias1
ORDER BY 3;

The logic is simple, to select dates which have even values for DAY.

Regards,

Adeel