By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,649 Members | 696 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,649 IT Pros & Developers. It's quick & easy.

Query to find a missing number

P: n/a


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



Jul 13 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Jul 13 '06 #2

P: n/a
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
Jul 13 '06 #3

P: n/a
>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_nbr) - 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.

Jul 15 '06 #4

P: n/a
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--" <jc*******@earthlink.netwrote in message
news:11**********************@m79g2000cwm.googlegr oups.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_nbr) - 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.

Jul 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.