Tags

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


T-SQL Query | [ The Missing Numbers Puzzle ]

Puzzle Statement

  1. We have a single input table called MissingNumbers.
  2. Here we have given some numbers in unordered manner.
  3. We have to pick the minimum and maximum number from the table and fill numbers if missed from the sequence.
  4. Please check out the sample input and expected output for details.

Sample Input

ID
5
1
2
8

Expected output

ID
1
2
3
4
5
6
7
8

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE”.
  • Your solution should be generic in nature.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

CREATE TABLE MissingNumbers
(
ID INT
)
GO

INSERT INTO MissingNumbers(ID) VALUES
(5),
(1),
(2),
(8)

UPDATE – 11-Apr-2015 – Solution 1 & 2 


--

--SOLUTION 1
SELECT DISTINCT Number FROM master..spt_values WHERE number <= ( SELECT MAX(ID) ID FROM MissingNumbers m1 ) AND number > 0

--SOLUTION 2
;WITH CTE AS
(
      SELECT TOP 1 m.ID FROM MissingNumbers m   ORDER BY ID
      UNION ALL
      SELECT ID + 1 FROM CTE c WHERE EXISTS (SELECT m.ID FROM MissingNumbers m WHERE m.ID > c.ID )
)
SELECT ID Number FROM CTE

--

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

Keep Learning

http://MSBISkills.com