T-SQL Query | [ Distinct A,B,C Puzzle ]

Puzzle Statement

1. We have a single input table called “DistinctSequence”.
2. We have to take distinct values from column COL_1
3. For second column COL_2 we have pick specific values corresponding to COL_1. Check out point 4.
4. The first distinct value from COL_1 is A so we have fetch the first record from Col_2 ; in this case the value 1. For B , we have pick the corresponding second value from COL_2 that is 2 in this case.
5. Please check out the sample input and expected output for details.

Sample Input

 COL_1 COL_2 A 14 A 2 A 3 B 1 B 12 B 3 C 12 C 2 C 3

Expected output

 COL_1 COL_2 A 1 B 2 C 3

Rules/Restrictions

• Your solution should be should use “SELECT” statement or “CTE”.
• Your solution should be generic in nature.

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` CREATE TABLE DistinctSequence ( COL_1 VARCHAR(1) ,COL_2 INT ) GO INSERT INTO DistinctSequence(COL_1,COL_2) VALUES ('A',1),('A',2),('A',3),('B',1),('B',2),('B',3),('C',1),('C',2),('C',3) --- ```

UPDATE – 20-Apr-2015 – Solution 1 & 2

 ``` -- --SOLUTION 1 ;WITH CTE1 AS ( SELECT COL_1 , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM ( SELECT col_1 , ROW_NUMBER() OVER (PARTITION BY col_1 ORDER BY col_1) rnk FROM DistinctSequence ) A WHERE rnk = 1 ) ,CTE2 AS ( SELECT COL_1,COL_2 , ROW_NUMBER() OVER (PARTITION BY COL_1 ORDER BY (SELECT NULL)) rnk FROM DistinctSequence ) SELECT a.COL_1 , b.COL_2 FROM CTE1 a INNER JOIN CTE2 b ON a.COL_1 = b.COL_1 AND a.rnk = b.rnk --SOLUTION 2 ( Emailed by ISHA MATTOO ) SELECT col_1,col_2 FROM ( SELECT col_1,col_2 ,ROW_NUMBER() OVER (PARTITION BY col_1 ORDER BY col_1) AS rnk_a ,DENSE_RANK() OVER (ORDER BY col_1) rnk FROM DistinctSequence ) t WHERE rnk_a = rnk ---- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com