RE: OT another SQL question

["BUTLER, Tony, FM" <roaming@xxxxxxx>]

Does this help?

select  'Missing Devices Between ' + convert(varchar, d.DeviceID) + '
and '
+
        (select convert(varchar,
min(DeviceID)) from Devices where
DeviceID > d.DeviceID)
from    Devices d
left    join (select DeviceID from Devices ) d2
        on d.DeviceID + 1 = d2.DeviceID
where   d2.DeviceID IS NULL
and     d.DeviceID < (select max(DeviceID) from
Devices)
order   by d.DeviceID

Doesn't list the missing numbers, but tells you where the gaps are....

Tony

> -----Original Message-----
> From: Dr John Tankard [mailto:john@xxxxxxx]
> Sent: 04 October 2002 14:30
> To: ukha_d@xxxxxxx
> Subject: RE: [ukha_d] OT another SQL question
> 
> 
> MS sql 2000
> 
> John
> 
> > -----Original Message-----
> > From: BUTLER, Tony, FM [mailto:roaming@xxxxxxx] 
> > Sent: 04 October 2002 14:21
> > To: 'ukha_d@xxxxxxx'
> > Subject: RE: [ukha_d] OT another SQL question
> > 
> > 
> > what flavour of SQL are u using - MS SQL?
> > 
> > > -----Original Message-----
> > > From: Dr John Tankard [mailto:john@xxxxxxx]
> > > Sent: 04 October 2002 14:09
> > > To: ukha_d@xxxxxxx
> > > Subject: RE: [ukha_d] OT another SQL question
> > > 
> > > 
> > > Its big 1...999999.
> > > 
> > > The only way I could think to do it was to create a table
> > that's auto 
> > > inc, insert 1000000 records and do a not in to a select 
> DeviceID in 
> > > Devices
> > > 
> > > John
> > > 
> > > > -----Original Message-----
> > > > From: BUTLER, Tony, FM [mailto:roaming@xxxxxxx]
> > > > Sent: 04 October 2002 13:56
> > > > To: 'ukha_d@xxxxxxx'
> > > > Subject: RE: [ukha_d] OT another SQL question
> > > > 
> > > > 
> > > > John,
> > > > 
> > > > What is the range of the numbers?
> > > > Do you really want it to list all missing from 1000 to
9999?
> > > > 
> > > > T.
> > > > 
> > > > > -----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
> > > > > 
> > > > > 
> > > > > 
> > > > > ------------------------ Yahoo! Groups Sponsor
> > > > > ---------------------~--> Home Selling? Try
Us!
> > > > > http://us.click.yahoo.com/QrPZMC/iTmEAA/MVfIAA/IBOolB/TM
> > > > >
--------------------------------------------------------------
> > > > > -------~->
> > > > > 
> > > > > 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/
> > > > > 
> > > > > 
> > > > 
> > > > 
> > > >
**************************************************************
> > > > *********
> > > >       Visit our Internet
site at http://www.rbsmarkets.com
> > > > 
> > > > This e-mail is intended only for the addressee named
> > above. As this 
> > > > e-mail may contain confidential or privileged 
> information, if you 
> > > > are not the named addressee, you are not authorised to
> > retain, read, 
> > > > copy or disseminate this message or any part of it. The
> > Royal Bank 
> > > > of Scotland is registered in Scotland No 90312
Registered 
> > Office: 36 
> > > > St Andrew Square, Edinburgh EH2 2YB
> > > > Regulated by the Financial Services Authority
> > > >
**************************************************************
> > > > *********
> > > > 
> > > > ------------------------ Yahoo! Groups Sponsor
> > > > ---------------------~--> Sell a Home for Top $ 
> > > http://us.click.yahoo.com/RrPZMC/jTmEAA/MVfIAA/IBOolB/TM
> > >
--------------------------------------------------------------
> > > -------~->
> > > 
> > > 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
> > > ---------------------~-->
> > > Plan to Sell a Home?
> > > http://us.click.yahoo.com/J2SnNA/y.lEAA/MVfIAA/IBOolB/TM
> > >
--------------------------------------------------------------
> > > -------~->
> > > 
> > > 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 ---------------------~-->
> > 4 DVDs Free +s&p Join Now
> > http://us.click.yahoo.com/pt6YBB/NXiEAA/MVfIAA/IBOolB/TM
> >
--------------------------------------------------------------
> > -------~->
> > 
> 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 
> ---------------------~-->
> 4 DVDs Free +s&p Join Now
> http://us.click.yahoo.com/pt6YBB/NXiEAA/MVfIAA/IBOolB/TM
> --------------------------------------------------------------
> -------~->
> 
> 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/
> 
> 


***********************************************************************
      Visit our Internet site at http://www.rbsmarkets.com

This e-mail is intended only for the addressee named above.
As this e-mail may contain confidential or privileged information,
if you are not the named addressee, you are not authorised to
retain, read, copy or disseminate this message or any part of it.
The Royal Bank of Scotland is registered in Scotland No 90312
Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB 
Regulated by the Financial Services Authority
***********************************************************************


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.