473,574 Members | 2,422 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query to find a missing 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



Jul 13 '06 #1
4 25497
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
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
>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.

Jul 15 '06 #4
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*******@eart hlink.netwrote in message
news:11******** **************@ 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.

Jul 17 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3024
by: nospam | last post by:
Hi, I have an application whose textareas rows automatically resize onFocus to the number of lines in the textArea. The cols are set to 100% (in a CSS file) in order to always take the full browser width I use textarea.value.split('\n') to find the number of lines. Some lines are longer than the 100% width and they wrap. However, wrappeed...
1
3669
by: sunilkeswani | last post by:
Hi I am still new to access. I want to know how i can build a query which can display results from 4 different columns/fields Like. Field1 Field2 Field3 Field4 1 2 1 0 1 1 0 0
5
2435
by: le0 | last post by:
Hello guys, Im really having a hard time doing this, I have a record set with the ItemNo field with the data type as Text. In the record that I have, I want to find the missing number in the series for example: 1 3 4.1
3
24019
by: sharugan | last post by:
Hi All, I have requirement to find out the number of pages in a document. The document could be DOC , TXT ,PDF images etc.I want to be able to upload and find out number of pages in an uploaded document. Can any expert guid me a way in PHP to accomblish this tak. Help would be appreciated.! thanks'
5
2558
debasisdas
by: debasisdas | last post by:
A ten-digit number contains every digit from 0 to 9. The digits are arranged so that the number formed by the first two digits, reading from left to right, is divisible by 2, the number formed by the first 3 digits is divisible by 3, the first 4 by 4, and so on until the whole number is divisible by 10. can you please find the number ...!
11
7684
by: C C++ C++ | last post by:
Hi all, got this interview question please respond. How can you quickly find the number of elements stored in a a) static array b) dynamic array ? Rgrds MA
3
2232
by: ankitamca85 | last post by:
query to get number of columns in table ???????????
0
7815
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8079
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6481
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5321
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3770
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2254
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1081
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.