|
|
Posted by Tony Rogerson on 07/17/06 13:33
> 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@earthlink.net> wrote in message
news:1152997498.634721.240010@m79g2000cwm.googlegroups.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.
>
[Back to original message]
|