Select values of a table with distinct values of a specific field of that table

Question

Suppose this DB table:

DECLARE @SUBSTITUTE TABLE(SUBSTITUTECODE varchar(25), COLORCODE varchar(15), SIZEPOS smallint);

INSERT INTO @SUBSTITUTE(SUBSTITUTECODE, COLORCODE, SIZEPOS) VALUES
('002252200005001', 'BLK', 2),
('002252200005002', 'BLK', 3),
('002252200005004', 'BLK', 5),
('002252200005005', 'BLK', 6),
('002252200005006', 'BLK', 10),
('002252200005007', 'BLK', 11),
('0022522005003', 'BLK', 4),
('0022522005004', 'BLK', 5);

I want to get all SUBSTITUTECODE, COLORCODE, SIZEPOS where SIZEPOS is unique - taking the first iteration of each duplicate value.

SUBSTITUTECODE	COLORCODE	SIZEPOS
002252200005001	BLK	2
002252200005002	BLK	3
002252200005004	BLK	5
002252200005005	BLK	6
002252200005006	BLK	10
002252200005007	BLK	11
0022522005003	BLK	4

The closest similar question comes from MySQL

Thank you in advance!

Your data doesn't make sense to me. Whence is the source of the value `MAY` which appears in the output? — Tim Biegeleisen, Sep 26 '21 at 01:15
Sorry, for the declaration of the table I used the abbreviation of Black color in English, but in the table I accidentally used the abbreviation of the color in another language! Correcting it now! — Faye D., Sep 26 '21 at 01:17
"taking the first iteration of each duplicate value." : First according to which order?!? Please remember that the table is SET of un-ordered rows and without `ORDER BY` the order is not guaranteed — Ronen Ariely, Sep 26 '21 at 04:16

score 0 · Answer 1 · answered Sep 26 '21 at 04:23

Please check if this solve your needs

DECLARE @SUBSTITUTE TABLE(SUBSTITUTECODE varchar(25), COLORCODE varchar(15), SIZEPOS smallint);

INSERT INTO @SUBSTITUTE(SUBSTITUTECODE, COLORCODE, SIZEPOS) VALUES
('002252200005001', 'BLK', 2),
('002252200005002', 'BLK', 3),
('002252200005004', 'BLK', 5),
('002252200005005', 'BLK', 6),
('002252200005006', 'BLK', 10),
('002252200005007', 'BLK', 11),
('0022522005003', 'BLK', 4),
('0022522005004', 'BLK', 5);

;With MyCTE AS (
    SELECT SUBSTITUTECODE, COLORCODE, SIZEPOS
        , RN = row_number() over (partition by SIZEPOS order by SUBSTITUTECODE) -- I assume that "first" you mean when ordering by SUBSTITUTECODE
    FROM @SUBSTITUTE
)
SELECT SUBSTITUTECODE, COLORCODE, SIZEPOS
FROM MyCTE
WHERE RN = 1

Select values of a table with distinct values of a specific field of that table

1 Answers1