Tags

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


SQL Puzzle | The Asia Cup Puzzle

In this puzzle you have a table called Team and each team from the team table will play with each other once. You have a write a T-SQL for the same. This was asked to my friend at a recent technical interview.

For details please check out the sample input and the expected output below.

Sample Inputs

TeamName
India
Pakistan
Sri Lanka
Bangladesh

Expected Output

TeamA TeamB
Bangladesh India
India Pakistan
Bangladesh Pakistan
India Sri Lanka
Pakistan Sri Lanka
Bangladesh Sri Lanka

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE Team
(
	TeamName VARCHAR(10)
)
GO

INSERT INTO Team
VALUES ('India'),('Pakistan'),('Sri Lanka'),('Bangladesh')
GO

--

SOLUTION – 1


--

SELECT 
	T1.TeamName TeamA, T2.TeamName TeamB 
FROM 
	Team T1 CROSS APPLY Team T2 
WHERE 
	T1.TeamName < T2.TeamName

--

SOLUTION – 2


--

SELECT 
	T1.TeamName TeamA, T2.TeamName TeamB 
FROM 
	Team T1 JOIN Team T2 
ON 
	T1.TeamName < T2.TeamName

--

SOLUTION – 3


--

SELECT a.TeamName TeamA , r.TeamName TeamB FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM Team) a 
CROSS APPLY
( 
	SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM Team) b WHERE b.rnk < a.rnk 
)r

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Advertisements