detecting gaps on an ID column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

detecting gaps on an ID column

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-03-01 : 18:09:35

Hello,

I have a very simple table with a value of "carID" (Integer)

I have about 45,000 rows in here, the carID's are in no particular order and I'm looking to get 1 piece of information out of it.

I want to know the 2 closest records. By this I mean what 2 values in the table have the least difference.

The table currently has a difference of 60 million between the max and minimum, with only 45k rows.

How could I query something like this ?

Any help is greatly appreciated.

Thanks!~
mike123

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-01 : 18:17:19

SELECT TOP 2 carID, ROW_NUMBER() OVER (ORDER BY carID) - carID FROM myTable ORDER BY 2, 1

Warning: not tested, not 100% I understand the request, but it should give you a start.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-02 : 02:34:21

Declare @MinRNo int
declare @t table (col1 int)
insert into @t values (10),(15),(40),(20)

Select Top 1 @MinRNo=X.Rno
From (select col1,ROW_NUMBER() over (order by col1) Rno from @t) X
Left Join (select col1,ROW_NUMBER() over (order by col1) Rno from @t) Y on X.Rno=Y.Rno+1
Where X.col1-Y.col1>0
order by (X.col1-Y.col1)

Select Col1
from (select col1,ROW_NUMBER() over (order by col1) Rno from @t)X
Where Rno between @MinRNo-1 and @MinRNo

Cheers
MIK

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-03-02 : 15:49:48

quote:
Originally posted by robvolk

SELECT TOP 2 carID, ROW_NUMBER() OVER (ORDER BY carID) - carID FROM myTable ORDER BY 2, 1

Warning: not tested, not 100% I understand the request, but it should give you a start.

awesome thanks! this gets me 2 rows brought back, and I can calculate that the difference between them is 841. I'm not able to validate the results are accurate, but I'm confident they are :)

I don't want to push my luck, but there is a layer of advanced functionality to add to this that would make things a dream to look at. It would also probably make it significantly more awesome!

Would it be possible to not only return the 1 set of closest results as per this query, but return a list from the closest to the furthest apart with a count for each row ?

for example it could be formed like this

highNum, lowNum, DIFFERENCE, INSTANCES

5050, 5000, 50 , 9
6050, 5000, 1050 , 5
7750, 6000, 1750 , 2

again any help is greatly appreciated!!!

Thanks once again!
mike123

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-02 : 16:21:05

See if this helps:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=157621

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-02 : 22:23:33

>> I have a very simple table with a value of "carID" (Integer) <<

The rest of the world used a VIN to identify a car ..

>> I have about 45,000 rows in here, the carID's are in no particular order .. <<

Yes, tables have no ordering by definition. Do you mean these integers are random? Negative?

>> I want to know the 2 closest records [sic]. By this I mean what 2 values in the table have the least difference. <<

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

CREATE TABLE Foobar
(car_id INTEGER NOT NULL PRIMARY KEY,
..);

SELECT F1.car_id AS car_1, F2.car_id AS car_2
FROM Foobar AS F1, Foobar AS F2
WHERE F1.car_id > F2.car_id
AND (F1.car_id – F2.car_id)
= (SELECT MIN(F3.car_id - F4.car_id)
FROM Foobar AS F3, Foobar AS F4
WHERE F3.car_id > F4.car_id);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources