Minimum missing value - Query help

Question

Post reply

Minimum missing value - Query help

Smash125

Hall of Fame

Points: 3562
More actions
July 13, 2012 at 3:13 am

#261113

CREATE TABLE #Contact
(
ContactID INT,
FirstName NVARCHAR(255),
MiddleName NVARCHAR(255),
LastName NVARCHAR(255),
)
INSERT INTO #Contact(ContactID,FirstName,MiddleName,LastName)
VALUES(2,'a','b','c'),
(3,'d','e','f'),
(4,'g','h','j'),
(5,'i','j','k'),
(6,'l','m','n'),
(8,'o','p','q')
Need to find minimum missing value using the above DDL statements
Here the minimum missing value is 1
then the next minimum missing value is 7
Tried with all the options could not come up with the logic .Please help me to solve this problem

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Trivial to solve with a numbers table.

CREATE TABLE Numbers (

Number INT PRIMARY KEY

)

INSERT INTO Numbers (Number)

SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT 1))

FROM msdb.sys.COLUMNS

With that

CREATE TABLE #Contact

(

ContactID INT,

FirstName NVARCHAR(255),

MiddleName NVARCHAR(255),

LastName NVARCHAR(255),

)

INSERT INTO #Contact(ContactID,FirstName,MiddleName,LastName)

VALUES(2,'a','b','c'),

(3,'d','e','f'),

(4,'g','h','j'),

(5,'i','j','k'),

(6,'l','m','n'),

(8,'o','p','q')

SELECT Number FROM dbo.Numbers WHERE

Number NOT IN (SELECT ContactID FROM #Contact AS c)

AND Number < (SELECT MAX(ContactID) FROM #Contact)

There are many, many other ways. If you're interested, google for Itzik Ben-Gan gaps and islands

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101099 More actions · Answer 2

Presumably the ContactID is indexed.

If there can only be single-line gaps, you can do this:

SELECT

c1.ContactID - 1 AS Missing_Number

FROM #Contact c1

WHERE NOT EXISTS (

SELECT 1 FROM #Contact c2 WHERE c2.ContactID = c1.ContactID - 1)

For multiple number gaps, you can do this:

-- add a row that leaves a multi-number gap

INSERT INTO #Contact(ContactID,FirstName,MiddleName,LastName) VALUES(12,'aaa','bbb','ccc')

SELECT

(SELECT ISNULL(MAX(ContactID), 0) + 1 FROM #Contact c3 WHERE c3.ContactID < c1.ContactID) AS Begin_Number,

c1.ContactID - 1 AS End_Number

FROM #Contact c1

WHERE NOT EXISTS (

SELECT 1 FROM #Contact c2 WHERE c2.ContactID = c1.ContactID - 1)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".