Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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.
  • 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

Advertisements