Query to find a missing number

**Ferrarista XXL** · Jul 13 '06, 05:45 PM

Re: Query to find a missing number

Mahesh BS wrote:

>Hello,
I need to write a query to find out a set of missing number in a given
>sequence.
>Eg : a Column in some table has the following data
>Col1
>1
>2
>3
>4
>5
>6
>8
>9
>10
>Here I need to write a query to find out that number 7 is missing in the
>given sequence.
>One possible solution is by using any loop. But I am looking out if the same
>can be achieved using any query.
>Thanks in advance.
>Regards,
>Mahesh
>
>
>

after some tests, i am arrived to this:

select (a.col1 + 1)
from tab1 a
where not exists
(select 1
from tab1 b
where b.col1 = (a.col1 + 1))
and a.col1 not in
(select max(c.col1)
from tab1 c)
order by 1

try it!
fabio

--
.... per questo oggi si dice:"Davanti alla Rossa c'è solo la pista..."
f.

Correr, competir, eu levo isso no sangue, é parte de minha vida
Ayrton Senna da Silva

leva UNA MARCIA per rispondermi in privato

**Hugo Kornelis** · Jul 13 '06, 09:25 PM

Re: Query to find a missing number

On Thu, 13 Jul 2006 21:43:21 +0530, Mahesh BS wrote:

>
>
>Hello,
>
>
>
I need to write a query to find out a set of missing number in a given
>sequence.

Hi Mahesh,

Check out http://www.aspfaq.com/show.asp?id=2516, under the heading
"Finding IDENTITY gaps".

--
Hugo Kornelis, SQL Server MVP

**--CELKO--** · Jul 15 '06, 09:05 PM

Re: Query to find a missing number

>I need to write a query to find out a set of missing number in a given sequence.<<

Here is a classix version of this problem:

Let's assume we have a table of people who bought tickets that are
supposed to be in sequential order and we want to make a list of what
is missing in each buyer's set of tickets.

CREATE TABLE Tickets
(buyer CHAR(5) NOT NULL,
ticket_nbr INTEGER DEFAULT 1 NOT NULL
CHECK (ticket_nbr 0),
PRIMARY KEY (buyer, ticket_nbr));

INSERT INTO Tickets VALUES ('a', 2);
INSERT INTO Tickets VALUES ('a', 3);
INSERT INTO Tickets VALUES ('a', 4);
INSERT INTO Tickets VALUES ('b', 4);
INSERT INTO Tickets VALUES ('c', 1);
INSERT INTO Tickets VALUES ('c', 2);
INSERT INTO Tickets VALUES ('c', 3);
INSERT INTO Tickets VALUES ('c', 4);
INSERT INTO Tickets VALUES ('c', 5);
INSERT INTO Tickets VALUES ('d', 1);
INSERT INTO Tickets VALUES ('d', 6);
INSERT INTO Tickets VALUES ('d', 7);
INSERT INTO Tickets VALUES ('d', 9);
INSERT INTO Tickets VALUES ('e', 10);

If we can assume that there is a relatively small number of Tickets,
then you could use a table of sequential numbers from 1 to (n) and
write:

SELECT DISTINCT T1.buyer, S1.seq
FROM Tickets AS T1, Sequence AS S1
WHERE seq <= (SELECT MAX(ticket_nbr) -- set the range
FROM Tickets AS T2
WHERE T1.buyer = T2.buyer)
AND seq NOT IN (SELECT ticket_nbr -- get missing numbers
FROM Tickets AS T3
WHERE T1.buyer = T3.buyer);

Another version:

BEGIN
SELECT *
INTO #foobar
FROM Tickets
UNION ALL
SELECT DISTINCT buyer, 0
FROM Tickets;

SELECT T1.buyer,
(T1.ticket_nbr + 1) AS gap_start,
(MIN(T2.ticket_ nbr) - 1) AS gap_end
FROM --Tickets AS T1,
#foobar AS T1,
Tickets AS T2
WHERE T1.ticket_nbr < T2.ticket_nbr
AND T1.buyer = T2.buyer
GROUP BY T1.buyer, T1.ticket_nbr
HAVING MIN(T2.ticket_n br) - T1.ticket_nbr 1;

END;

The trick here is to add a zero to act as a boundary when 1 is missing
from the sequence.

In Standard SQL-92, you could write the UNION ALL expression directly
in the FROM clause.

**Tony Rogerson** · Jul 17 '06, 01:35 PM

Re: Query to find a missing number

BEGIN

SELECT *
INTO #foobar
FROM Tickets
UNION ALL
SELECT DISTINCT buyer, 0
FROM Tickets;

Does this mean when other people use temporary tables (like you've just
done) then they aren't going to get a bashing for 'procedural programming'
and imiatating a magentic tape file system?

Perhaps you've finally woke up to writing SQL for production rather than for
a book!

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@eart hlink.netwrote in message
news:1152997498 .634721.240010@ m79g2000cwm.goo glegroups.com.. .

>

>>I need to write a query to find out a set of missing number in a given
>>sequence.<<

>
Here is a classix version of this problem:
>
Let's assume we have a table of people who bought tickets that are
supposed to be in sequential order and we want to make a list of what
is missing in each buyer's set of tickets.
>
CREATE TABLE Tickets
(buyer CHAR(5) NOT NULL,
ticket_nbr INTEGER DEFAULT 1 NOT NULL
CHECK (ticket_nbr 0),
PRIMARY KEY (buyer, ticket_nbr));
>
INSERT INTO Tickets VALUES ('a', 2);
INSERT INTO Tickets VALUES ('a', 3);
INSERT INTO Tickets VALUES ('a', 4);
INSERT INTO Tickets VALUES ('b', 4);
INSERT INTO Tickets VALUES ('c', 1);
INSERT INTO Tickets VALUES ('c', 2);
INSERT INTO Tickets VALUES ('c', 3);
INSERT INTO Tickets VALUES ('c', 4);
INSERT INTO Tickets VALUES ('c', 5);
INSERT INTO Tickets VALUES ('d', 1);
INSERT INTO Tickets VALUES ('d', 6);
INSERT INTO Tickets VALUES ('d', 7);
INSERT INTO Tickets VALUES ('d', 9);
INSERT INTO Tickets VALUES ('e', 10);
>
If we can assume that there is a relatively small number of Tickets,
then you could use a table of sequential numbers from 1 to (n) and
write:
>
SELECT DISTINCT T1.buyer, S1.seq
FROM Tickets AS T1, Sequence AS S1
WHERE seq <= (SELECT MAX(ticket_nbr) -- set the range
FROM Tickets AS T2
WHERE T1.buyer = T2.buyer)
AND seq NOT IN (SELECT ticket_nbr -- get missing numbers
FROM Tickets AS T3
WHERE T1.buyer = T3.buyer);
>
Another version:
>
BEGIN
SELECT *
INTO #foobar
FROM Tickets
UNION ALL
SELECT DISTINCT buyer, 0
FROM Tickets;
>
SELECT T1.buyer,
(T1.ticket_nbr + 1) AS gap_start,
(MIN(T2.ticket_ nbr) - 1) AS gap_end
FROM --Tickets AS T1,
#foobar AS T1,
Tickets AS T2
WHERE T1.ticket_nbr < T2.ticket_nbr
AND T1.buyer = T2.buyer
GROUP BY T1.buyer, T1.ticket_nbr
HAVING MIN(T2.ticket_n br) - T1.ticket_nbr 1;
>
END;
>
The trick here is to add a zero to act as a boundary when 1 is missing
from the sequence.
>
In Standard SQL-92, you could write the UNION ALL expression directly
in the FROM clause.
>

Query to find a missing number

Query to find a missing number

Comment

Comment

Comment

Comment