Home > Archive > PHP SQL > April 2004 > finding a missing data in a table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
finding a missing data in a table
|
|
| davidklonski 2004-04-21, 7:30 am |
| Hello
I have a table which a column holding UserIds of type bigint(5)
Usually I will have consecutive UserIds in the table, i.e.: 1,2,
3,4,...
However, it is possible that the table will have whole holes in the
data: 1,2,5,6, 8,...
Is it possible to detect such holes using SQL?
for example, if the data is: 1,2,5,6,8
then I would like to get the lowest missing id. In the above example
it would be 3
thanks in advance
----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums
| |
| Andy Hassall 2004-04-21, 6:35 pm |
| On Wed, 21 Apr 2004 05:45:29 -0500,
davidklonski@fastmail-dot-fm.no-spam.invalid (davidklonski) wrote:
>I have a table which a column holding UserIds of type bigint(5)
>
>Usually I will have consecutive UserIds in the table, i.e.: 1,2,
>3,4,...
>However, it is possible that the table will have whole holes in the
>data: 1,2,5,6, 8,...
>Is it possible to detect such holes using SQL?
>for example, if the data is: 1,2,5,6,8
>then I would like to get the lowest missing id. In the above example
>it would be 3
Create a table containing a complete contiguous range of numbers, and either
use MINUS, or an LEFT OUTER JOIN looking for the introduced NULLs on the right
hand side.
Why are the gaps significant anyway?
--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space
|
|
|
|
|