Tags

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


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

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-the-work-order-puzzle/
https://msbiskills.com/2016/08/19/sql-puzzle-the-order-by-puzzle/
https://msbiskills.com/2017/01/03/sql-puzzle-order-by-using-parent-child-relationship-2/
https://msbiskills.com/2017/12/17/sql-puzzle-not-the-last-order-puzzle/
https://msbiskills.com/2017/12/22/sql-puzzle-the-order-by-puzzle-1/

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 Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements