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

Expected output

Rules/Restrictions

• Your solution should be should use “SELECT” statement or “CTE”.
• Your solution should be generic in nature.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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