SQL Puzzle | DISTINCT + Can you ORDER BY this data? | SQL Interview Question

In this puzzle we have to make the distinct data ( DISTINCT from COLUMN [Name] AND Id ) and provide output in the required format using ORDER BY

Please check the sample input and the expected output.

Sample Input

Name Id
X 1
X 1
X 9
X 10
X 1
A 4
A 2
A 4
A 6
U 4
U 12
U 9
B 5
B 5
B 6
B 6
B 7
B 7
B 8

Expected Output

Name Id
X 1
X 9
X 10
A 2
A 4
A 6
U 4
U 9
U 12
B 5
B 6
B 7
B 8

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE testOrderBY1 ( [Name] VARCHAR(1) ,Id INT ) GO INSERT INTO testOrderBY1 VALUES ('X', 1),('X', 1),('X', 9),('X', 10),('X', 1) ,('A', 4),('A', 2),('A', 4),('A', 6) ,('U', 4),('U', 12),('U', 9) ,('B', 5),('B', 5),('B', 6),('B', 6),('B', 7),('B', 7),('B', 8) GO SELECT * FROM testOrderBY1 GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

SOLUTION – 1 | Using ORDER BY … OVER (PARTITION BY ..) Clause

 ```-- SELECT * FROM testOrderBY1 GROUP BY [Name] , Id ORDER BY MIN(Id) OVER (PARTITION BY [Name]), Id -- ```

Output-1

 ```-- Name Id ---- ----------- X 1 X 9 X 10 A 2 A 4 A 6 U 4 U 9 U 12 B 5 B 6 B 7 B 8 (13 rows affected) -- ```

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Related Puzzles

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/