Tags

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


SQL Puzzle | Top 2 names in each class without ranking functions

In this puzzle we have to get top 2 Names based on Id for each Class and the restriction is that you cannot use any ranking functions for it. Please check the sample input and expected output.

Sample Input

Id Name Class
1 Pawan A
2 Ramesh A
3 Krishan kant A
4 Sharlee B
5 Diwan B
6 Adam C
7 Avtaar C
8 Kishan C
9 Bhalero C
10 Mangesh D
11 DK E
12 TD E

Expected Output

Id Name Class
2 Ramesh A
3 Krishan kant A
4 Sharlee B
5 Diwan B
8 Kishan C
9 Bhalero C
10 Mangesh D
11 DK E
12 TD E

Script

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

--

/*SELECT top 2 Names based on Id for each Class | You cannot use any ranking functions*/

CREATE TABLE Groups
(
	 Id INT
	,Name VARCHAR(20)
	,Class CHAR(1)
)
GO

INSERT INTO Groups VALUES
(1,'Pawan','A'),
(2,'Ramesh','A'),
(3,'Krishan kant','A'),
(4,'Sharlee','B'),
(5,'Diwan','B'),
(6,'Adam','C'),
(7,'Avtaar','C'),
(8,'Kishan','C'),
(9,'Bhalero','C'),
(10,'Mangesh','D'),
(11,'DK','E'),
(12,'TD','E')
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 CROSS APPLY 

--

/* SOLUTION - USING CROSS APPLY */

SELECT u.* FROM
(
	select distinct class 
	from Groups
)X
CROSS APPLY 
(
	SELECT TOP 2 * FROM
	Groups G WHERE G.Class = X.Class
	ORDER BY G.Id DESC
)u
ORDER BY Id

--

Output-1

--

/*------------------------
/* SOLUTION - USING CROSS APPLY */

SELECT u.* FROM
(
	select distinct class 
	from Groups
)X
CROSS APPLY 
(
	SELECT TOP 2 * FROM
	Groups G WHERE G.Class = X.Class
	ORDER BY G.Id DESC
)u
ORDER BY Id
------------------------*/
Id          Name                 Class
----------- -------------------- -----
2           Ramesh               A
3           Krishan kant         A
4           Sharlee              B
5           Diwan                B
8           Kishan               C
9           Bhalero              C
10          Mangesh              D
11          DK                   E
12          TD                   E

(9 row(s) affected)



--

Solution – 2 | USING Custom Ranking 

--

/* SOLUTION - Custom Ranking */
SELECT Id,Name,Class 
FROM
(
	SELECT *  , (SELECT COUNT(*) FROM Groups X
			WHERE G.Class = X.Class AND G.Id < X.Id	) rnk
	FROM Groups G
)u WHERE rnk < 2
--

Output-2

--

/*------------------------

/* SOLUTION - Custom Ranking */
SELECT Id,Name,Class 
FROM
(
	SELECT *  , (SELECT COUNT(*) FROM Groups X
			WHERE G.Class = X.Class AND G.Id < X.Id	) rnk
	FROM Groups G
)u WHERE rnk < 2
------------------------*/
Id          Name                 Class
----------- -------------------- -----
2           Ramesh               A
3           Krishan kant         A
4           Sharlee              B
5           Diwan                B
8           Kishan               C
9           Bhalero              C
10          Mangesh              D
11          DK                   E
12          TD                   E

(9 row(s) affected)


--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com