RE: OT another SQL question

["Alex Monaghan" <alex@xxxxxxx>]

I guess you'd have to generate a temporary table with the complete range
and
do a compare against it or do a simple cursor to step though the table
and
compare last value with current.

Something along the lines of (code will need tidy up for specific SQL
server, but should show you the basic outline)

declare       #my_id     
int,
           
#current_id      int
select #current_id = 1000
declare cursor my_cursor for select deviceid from devices
fetch next my_cursor into #my_id
while not EOF  -- use the @@FETCHSTATUS variable in MS-SQL, not sure
about
other dialects
begin
      while #current_id < #my_id
      begin
            select
#current_id
            select
#current_id = #current_id + 1
      end
      fetch next my_cursor into #my_id
end

Simple, ugly, but should do the job

> -----Original Message-----
> From: Dr John Tankard [mailto:john@xxxxxxx]
> Sent: 04 October 2002 13:52
> To: ukha_d@xxxxxxx
> Subject: [ukha_d] OT another SQL question
>
>
> Sorry for the OT post
>
> Is it possible in SQL to do this :-
>
> I have a table:-
>
> Devices
> --------
> DeviceID
> ..
>
>
> And in the table there are some missing device numbers ie
>
> 1000
> 1001
> 1003
> 1004
>
> In SQL is it possible to list missing numbers ie 1002 ?
>
> John
>
>
>
>
> http://www.automatedhome.co.uk
> Post message: ukha_d@xxxxxxx
> Subscribe:  ukha_d-subscribe@xxxxxxx
> Unsubscribe:  ukha_d-unsubscribe@xxxxxxx
> List owner:  ukha_d-owner@xxxxxxx
>
> List of UKHA Groups here - http://groups.yahoo.com/group/UKHA_Grouplists/
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>

Yahoo! Groups Sponsor

ADVERTISEMENT

http://www.automatedhome.co.uk
Post message: ukha_d@xxxxxxx 
Subscribe:  ukha_d-subscribe@xxxxxxx 
Unsubscribe:  ukha_d-unsubscribe@xxxxxxx 
List owner:  ukha_d-owner@xxxxxxx
List of UKHA Groups here - http://groups.yahoo.com/group/UKHA_Grouplists/

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.