SQL for numeric ranges

Question

SQL for numeric ranges

lionfan91

SSCrazy Eights

Points: 8794
More actions
December 5, 2007 at 7:34 am

#177226

I store a table that contains a bunch of numbers which are "used IDs". These aren't sequentially used ID values. I'm looking for SQL that will get me the ranges of numbers that are used. For example, let's say my table contains:
100
104
105
106
110
111
115
I'd like the SQL query to return the following records with START and END fields:
100, NULL
104, 106
110, 111
115, NULL
I'm sure this is probably easy but for the life of me it's escaping me. Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 1

It's easier to find missing ID's... this test code shows you how... last paragraph of code is the code that does the work... everything else is just test setup...

--===== Setup for speed and to prevent blocking

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--=============================================================================

-- Create an experimental table to simulate the table being examined

--=============================================================================

--===== If the experimental temp table exists, drop it so we can do repeat runs

-- if we want to "play".

IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL

DROP TABLE #yourtable

--===== Create the experimental temp table and populate with IDs on the fly

SELECT TOP 1000000 ID = IDENTITY(INT, 1, 1)

INTO #yourtable

FROM Master.dbo.SYSCOLUMNS sc1,

dbo.SYSCOLUMNS sc2

--===== Like any good table, our experimental table needs a Primary Key

ALTER TABLE #yourtable

ADD PRIMARY KEY CLUSTERED (ID)

--===== Ok, we have an experimental table with a million IDs from 1 to 1,000,000.

-- Let's remove some of the rows and see if we can find if they're missing

-- This deletes a handful of individual rows and 3 very small ranges

DELETE #yourtable

WHERE ID IN (2,3,250,251,2000,4000,4002,4004,900001,900002,900003,999999)

-- This deletes a "monster" range just to see how it's handled.

DELETE #yourtable

WHERE ID BETWEEN 500000 AND 700000

or (ID BETWEEN 700000 AND 800000

AND ID%2 = 0)

--===== This short little ditty is what does the actual work

SELECT MinRange = (SELECT ISNULL(MAX(suba.ID),0)+1

FROM #yourtable suba

WHERE suba.ID < a.ID),

MaxRange = ID - 1

FROM #yourtable a

WHERE a.ID - 1 NOT IN (SELECT ID FROM #yourtable)

AND a.ID - 1 > 0

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 2

Jeff... thanks for the reply. That code works like a champ, but unfortunately I need the IDs that are present, not the ones missing. My ID range will be from 1 to around 4 billion (I kid you not) and the user requirements don't specify how the IDs should be used, so that can be assigned any way the user wants. This is a government project, so that might explain a lot! 😉 Basically the IDs are assigned to four different object types and the IDs can't be reused among them. So if instances of object A are assigned IDs 1, 2, and 3, then instances object B thru D can't use 1, 2, 3. However, they could use ID 4 for an object B, resuming with ID 5 for the next object A. Confusing enough? Yeah, gotta love it. Anyway, I need to be able to show what ID ranges are already used so the users can pick from the unused ones. I'm only storing the used IDs in my table -- don't want to store 4 billion entries if I can help it!

Steve

Sergiy SSC Guru Points: 110208 More actions · Answer 3

to show what ID ranges are already used so the users can pick from the unused ones.

So, users actually need unused ID's, not used ones?

:hehe:

Why don't you want to display what users actually need?

_____________
Code for TallyGenerator

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 4

I need to generate a report listing what ID ranges have been used.

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 5

Then, try this...

http://www.sqlservercentral.com/articles/Advanced+Querying/anefficientsetbasedsolutionforislandsandgaps/1619/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 6

lionfan91

SSCrazy Eights

Points: 8794

December 10, 2007 at 6:31 am

#758558

Sweet! Thanks, I think that will do the trick.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 7

Jeff Moden (12/7/2007)
Then, try this...
http://www.sqlservercentral.com/articles/Advanced+Querying/anefficientsetbasedsolutionforislandsandgaps/1619/

It would be more readable if the title of the atricle is included in the url replacing hyphen for space instead of removing it

ex

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspxYou can clearly understand what that article is about

If you are moderating this site, suggest this to your admin 🙂

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 8

Heh... agreed... but I'm not a moderator... usually, I'm just part of the problem 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 9

Jeff Moden (12/28/2007)
Heh... agreed... but I'm not a moderator... usually, I'm just part of the problem 😛

Well. Shouldnt it be "I'm just part of the problem Solving"? [:)]

In fact you are eligible to be a moderator of the forum where you are highly active 🙂

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 10

Thanks for the compliment, but I don't actually have the time to be a moderator... would take away from problem-solving time. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)