|
|
|
|
|
|
| Author |
Message |
Mahesh BS
*nix forums beginner
Joined: 13 Jul 2006
Posts: 1
|
 Posted: Thu Jul 13, 2006 10:43 am Post subject:
Query to find amissing number
|
|
|
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 |
|
| Back to top |
|
 |
John Brock
*nix forums beginner
Joined: 08 Jun 2005
Posts: 13
|
| Posted: Thu Jul 13, 2006 8:15 pm Post subject:
Re: Query to find amissing number
|
|
|
In article <e957qq$8u$1@ns2.fe.internet.bosch.com>,
Mahesh BS <mahesh.bs@in.bosch.com> wrote:
| Quote: |
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.
|
This should work:
select a.Col1 from MyTable a
where not exists
(select b.Col1 from MyTable b where a.Col1 - 1 = b.Col1)
go
--
John Brock
jbrock@panix.com |
|
| Back to top |
|
|
John Brock
*nix forums beginner
Joined: 08 Jun 2005
Posts: 13
|
| Posted: Fri Jul 14, 2006 4:50 pm Post subject:
Re: Query to find amissing number
|
|
|
In article <e969ls$f0n$1@reader2.panix.com>,
John Brock <jbrock@panix.com> wrote:
| Quote: | In article <e957qq$8u$1@ns2.fe.internet.bosch.com>,
Mahesh BS <mahesh.bs@in.bosch.com> 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.
This should work:
select a.Col1 from MyTable a
where not exists
(select b.Col1 from MyTable b where a.Col1 - 1 = b.Col1)
go
|
Actually the above doesn't return the full list of numbers that
you want, it only alerts you to the location of the gaps in your
sequence, giving you a quick way to determine if any gaps exist.
(Quick if Col1 is indexed that is -- otherwise not so quick).
However by calling a related query (note "+" rather than "-"):
select a.Col1 from MyTable a
where not exists
(select b.Col1 from MyTable b where a.Col1 + 1 = b.Col1)
go
you can get enough information to figure out how big the gaps are,
and therefore what numbers the gaps contain. You are still going
to need a loop to examine the results of the two queries, but if
the number of rows involved is large and the number of gaps is
small this might (perhaps!) be more efficient than looping through
the entire table.
I don't think there is any way to just return the list of missing
numbers directly from a query. SQL queries work by operating on
a set of input rows to produce a set of output rows. Since the
number of missing rows in a sequence is completely arbitrary, I
don't how SQL could accomplish the necessary transformation from
input to output (although SQL has features I've never used, and
maybe one of them can do this).
--
John Brock
jbrock@panix.com |
|
| Back to top |
|
|
--CELKO--
*nix forums Guru Wannabe
Joined: 01 Jun 2005
Posts: 122
|
| Posted: Fri Jul 14, 2006 5:07 pm Post subject:
Re: Query to find amissing number
|
|
|
| Quote: | I need to write a query to find out a set of missing numbers in a given sequence.
|
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 top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Sun Nov 23, 2008 11:12 am | All times are GMT
|
|
Mortgages | MPAA | Personal Loans | Debt Consolidation | Xbox Mod Chip
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Sybase
