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

Expected Output

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