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_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.

.

Relevant Pages

  • Looking for a macro to automatically generate numbers in "cut-stack" order...
    ... tickets in "cut-stack" order. ... given "nnn" sheets of paper, the order of each ticket number is ... and the second page contains the following sequence of numbers: ... In the macro, I want to be prompted for the Start_Number, Stop_Number ...
    (microsoft.public.excel)
  • Re: Lottery Math? Question
    ... The odds against three particular tickets producing that sort of sequence are 1 in 77,000 approx, although the odds of that sort of run happening at any time are substantially less exciting because you either have to account for the number of eligible sequences or discount the first 'win'. ... If there was a convenient lottery outlet I'd buy a ticket for Wednesday's rollover (M£7 jackpot), ...
    (rec.gambling.lottery)
  • RE: Indicate missing number in a sequence (Part II)
    ... "mmock" wrote: ... only 500 tickets in the booklet. ... want it to count an out of sequence or just leave the cell blank. ... in a sequence is missing. ...
    (microsoft.public.excel.misc)
  • Re: Query to find amissing number
    ... Let's assume we have a table of people who bought tickets that are ... PRIMARY KEY (buyer, ticket_nbr)); ... FROM Tickets AS T1, Sequence AS S1 ... AND seq NOT IN (SELECT ticket_nbr -- get missing numbers ...
    (comp.databases.sybase)
  • Re: Thinking about code or SP
    ... But what can I modify if I want to get all the numbers inside the gap in one ... "John Gilson" wrote: ... > CREATE TABLE Tickets ... > If the sequence must begin at 1 and you need the preceding gap also, ...
    (microsoft.public.sqlserver.programming)