**Tags**

Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

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

**Puzzle Statement**

- We have a single input table called “DistinctSequence”.
- We have to take distinct values from column COL_1
- For second column COL_2 we have pick specific values corresponding to COL_1. Check out point 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.
- 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**

Neeraj

said:There is no need of partitioning in solution 2, see below :-

select distinct col_1, dense_rank() over (order by col_1) rnk from DistinctSequence

LikeLike

Pawan Kumar Khowal

said:Yes you are right !

LikeLike