How to find missing values in a sequence with SQL at Xaprb

How to find missing values in a sequence with SQL

Sometimes it is important to know which values in a sequence are missing, either to find unused values so they can be used, or to find “holes” in the data. In this article I’ll show you how to find missing values, how to find the start and end of ranges of missing values, and how to optimize the queries for best performance.

Exclusion joins

Possibly the most efficient technique, depending upon the application, is to use an exclusion join against a list of all legal values (for example, an integers table). For instance, at my current employer we assign a unique tracking ID to certain bits of data. For reasons lost in the mist of time, we use three-character combinations of letters and numbers. It’s effectively a base-36 number system. It is not the most efficient thing to work with in SQL! Since there is no magical built-in way to get the database to assign the next unused value in the sequence, we keep a table with all 36³ legal values, and do an exclusion join against the list of legal values. This is an acceptable way to find the next values, but of course it’s nowhere near optimal; when transactional consistency is needed, we have to lock tables up and do an expensive query. An identity (auto_increment) column would be preferable.

Putting three-character codes behind and assuming you want to analyze some existing data for holes without creating lists of legal values, it is possible to find missing values in a sequence by matching it against itself. For example, I am helping someone design a database to store information about gravestones. The original data was hand-entered into a spreadsheet, with a single column to keep track of gravestone numbers. There are duplicate and missing values in the sequence, both of which can indicate data problems, so it’s highly desirable to find and fix them. After importing the spreadsheets verbatim into a staging table, I ran a number of analyses to find data problems before transforming the data into the final tables.

The setup

If you want to follow along with the examples, you can run the following queries to create some sample data in MySQL:

create table sequence (
    id int not null primary key
);

insert into sequence(id) values
    (1), (2), (3), (4), (6), (7), (8), (9),
    (10), (15), (16), (17), (18), (19), (20);

Notice the values 5, 11, 12, 13, and 14 are missing from the sequence. If you are using Microsoft SQL Server, change sequence to #sequence from here on:

create table #sequence (id int not null primary key)

insert into #sequence(id)
    select 1
    union all select 2
    union all select 3
    union all select 4
    union all select 6
    union all select 7
    union all select 8
    union all select 9
    union all select 10
    union all select 15
    union all select 16
    union all select 17
    union all select 18
    union all select 19
    union all select 20

Finding duplicate and missing numbers

Finding duplicate numbers is easy:

select id, count(*) from sequence
group by id
having count(*) > 1;

In this case there are no duplicates, since I’m not concentrating on that in this post (finding duplicates is straightforward enough that I hope you can see how it’s done). I had to scratch my head for a second to find missing numbers in the sequence, though. Here is my first shot at it:

select l.id + 1 as start
from sequence as l
  left outer join sequence as r on l.id + 1 = r.id
where r.id is null;

The idea is to exclusion join against the same sequence, but shifted by one position. Any number with an adjacent number will join successfully, and the WHERE clause will eliminate successful matches, leaving the missing numbers. Here is the result:

start
5
11
21

Find ranges of missing values with subqueries

The above query identifies the start of ranges of missing numbers, but not the end. It also gives a false positive for 21, which is a missing number because it’s off the end of the whole sequence. I wanted to solve both problems. After squinting at it a while, I realized I could solve this problem with a correlated subquery, as follows:

select start, stop from (
  select m.id + 1 as start,
    (select min(id) - 1 from sequence as x where x.id > m.id) as stop
  from sequence as m
    left outer join sequence as r on m.id = r.id - 1
  where r.id is null
) as x
where stop is not null;

The final WHERE clause makes sure the upper end of the sequence isn’t counted as a hole. This can be written several ways, some of them without wrapping the whole thing in a subquery (for example, where r.id is null and r < (select max(id) from sequence)), but it suits me fine as it is. Here is the result:

start	stop
5	5
11	14

If the sequence is a another data type, such as dates or letters of the alphabet, it is often possible to use some other functions to get the “next” value in the sequence in the join condition. For example, if the id column is CHAR(1), here is a query for MySQL:

insert into sequence(id) values
    ('a'), ('b'), ('c'), ('e'),
    ('f'), ('g'), ('l'), ('m'), ('n');

select start, stop from (
    select char(ascii(m.id) + 1) as start,
        (select char(min(ascii(id)) - 1) from sequence as x where x.id > m.id) as stop
    from sequence as m
        left outer join sequence as r on ascii(m.id) = ascii(r.id) - 1
    where r.id is null
) as x
where stop <> '';

It’s necessary to change the final WHERE clause to stop <> '' because CHAR() skips NULLs, converting CHAR(NULL) to the empty string. Here is the result:

start	stop
d	d
h	k

Performance analysis: rewriting without subqueries

I don’t like correlated subqueries. In fact, I avoid subqueries if at all possible. Correlated subqueries are especially bad because, depending on the query optimizer, they may force the RDBMS to build a temporary table and probe into it for each value in the left-most table, which is O(n²). It dawned on me that the query could be written as left joins:

select l.id + 1 as start, min(fr.id) - 1 as stop
from sequence as l
    left outer join sequence as r on l.id = r.id - 1
    left outer join sequence as fr on l.id < fr.id
where r.id is null and fr.id is not null
group by l.id, r.id;

Of course, this is hardly, if at all, better. The < in the join condition makes the join essentially a CROSS JOIN, which is still an O(n²) join. Just to see how the query optimizer handles this, I ran it through both MySQL and Microsoft SQL server, and looked at the query plans. I filled the table with values up to 5000 (large enough that the DBMSs would create statistics on the index) and then made holes in the sequence as follows:

start	stop
5	5
11	14
100	100
855	855
1230	1230
2400	2500

SQL Server actually optimized the first query significantly better, which highlights one of my favorite principles: always measure performance, and never try to “optimize by eye!” Here are the numbers (see my article about using awk to sum up query statistics):

 -- query one --
Scans:                  9
Logical reads:         35
Physical reads:         0
Read-ahead reads:       0
CPU time:              10 ms
Elapsed time:          49 ms

 -- query two --
Scans:                  9
Logical reads:         77
Physical reads:         0
Read-ahead reads:       0
CPU time:              60 ms
Elapsed time:         148 ms

MySQL’s results did not vary as much; the execution time was .8% faster on the first query, and the EXPLAIN result showed that the second query caused ‘Range checked for each record’. The two query plans were actually very different, according to EXPLAIN.

Written by Xaprb

December 6th, 2005 at 11:00 am

Posted in Uncategorized

114 Responses to 'How to find missing values in a sequence with SQL'

Subscribe to comments with RSS

Works well, except that (as expected, I assume) seq 2,3,4 returns 5, where someone (i.e. me) would actually want 1.

Joe

24 Jan 06 at 4:25 am
Hi, I actually needed the opposite of the above. Instead of missing start and end, I need the non-missing start and end. How do I change the last query to do this?

Sebastian

22 Mar 06 at 5:50 am
Sebastian, I’m not completely clear on what you mean. Do you mean “instead of finding the start and end of missing ranges, find the start and end of contiguous ranges?”

Xaprb

22 Mar 06 at 10:05 am
Sebastian, please see Find contiguous ranges with SQL for an answer to the problem.

Xaprb

22 Mar 06 at 9:53 pm
Hi Xaprb,

Yes that’s what I needed. Thanks.

Sebastian

23 Mar 06 at 6:17 am
I was wondering if it would really be much more complicated, using the table structure above to simply find and return a list of missing numbers. Instead of ranges, simply list out like: 5, 11, 12, 13, 14, or somthing like that. Thank you.

Hess

28 Aug 06 at 7:10 pm
You could use some of the techniques I’ve outlined in How to simulate the GROUP_CONCAT function.

Xaprb

28 Aug 06 at 7:12 pm
Hi, I actually need to get the missing range from a column which will be in sequence on certain criterias in another column in that table. Can you please explain how this can be achieved. (My question may be silly but I don’t know how to do it. I am new to SQL).

Sara

22 Sep 06 at 8:55 am
Hi Sara, I’m afraid I don’t quite understand your question. Can you post an example data set and explain what the answer should be?

Xaprb

22 Sep 06 at 8:58 am
Hi, see below a table A with three columns the records will be as
```
a1     a2      a3
------------------
1      ddd    aaa
1      ooo    bbb
4      vvv    aaa
5      qqq    bbb
7      hhh    bbb
6      www    aaa
```
Now i want to ge the missing seq in the column a1 where a3 is ‘aaa’. The result should be some what like 2-3 and 5-5. Basically I need to get the missing range based on a critiera in column a3.

Thanks in Advance
Sara

22 Sep 06 at 9:36 am

Hi Sara, I think this will do what you need:

select start, stop from(
   select m.a1 + 1 as start,
   (select min(a1)-1 from sequence as x where x.a3 = 'aaa' and x.a1 > m.a1) as stop
   from sequence as m
      left outer join sequence as r on m.a3 = r.a3 and m.a1 = r.a1 - 1
   where r.a1 is null and m.a3 = 'aaa'
) as x
where stop is not null;

-- result:
+-------+------+
| start | stop |
+-------+------+
|     2 |    3 |
|     5 |    5 |
+-------+------+

Xaprb

22 Sep 06 at 10:32 am

Thank you very much. Is there a way to do this without sub-queries(just out of curiosity).

Sara

22 Sep 06 at 11:17 am
You’re welcome. The query I gave is just an adaptation of the query earlier in the article, and I showed some ways to avoid subqueries towards the end of the article. It’s just a matter of figuring out where to put the extra WHERE clauses, to restrict your data set to those rows that have ‘aaa’ in the a3 column.

Xaprb

22 Sep 06 at 11:25 am
Just in case anyone might find this useful: In my current job we use Hypersonic in our testing environment. Apparently HSQL didn’t like the condition in the left outer join. I took a stab at rewriting it using “NOT EXISTS” and seems to work pretty well.
```
select start, stop from ( select m.id + 1 as start, 
  (select min(id) - 1 from sequence as x where x.id > m.id) as stop
    from sequence as m
    where not exists (select id from sequence as r where m.id=r.id - 1 ) )
  as x
where stop is not null
```
Robert

30 Oct 06 at 1:24 pm
Thanks for this info, unfortunatly my seqs numbers to check are included in another sequence. i.e
```
col1   col2   
----    -----
1        1
1        3 
1        4
2        1 
2        3
2        4 
2        5
```
Where I what to check the out-of-seq in col2 for each col1… thanks
bob

9 Apr 07 at 3:20 pm
Is it possible to query for the Minimum ID (missing if possible, or new if not) in a way that would work in both SQL and JET?

Vitaly

19 Apr 07 at 10:25 am

You could also try this much faster sollution (on a big dataset) using “not exists”:

select (a.id + 1) as id from sequence a
where not exists
(select 1 from sequence b where b.id = (a.id+1) )
and a.id not in (select max(c.id) from sequence c)
order by id;

It will only give the first value in a gap, but combining it with an union like this:

(select (a.id + 1) as id from sequence a
where not exists
(select 1 from sequence b where b.id = (a.id+1) )
and a.id not in (select max(c.id) from sequence c))
union all (
select (a.id - 1) as id from sequence a
where not exists
(select 1 from sequence b where b.id = (a.id - 1) )
and a.id not in (select min(c.id) from sequence c)) 
order by id;

giving the result:
5
5
11
14
Then you can parse the result two by two (startvalue, endvalue in a gap), this is extremely fast on mysql 4.1 at least. giving a result in about a second or two on a table with over 200000 rows.

haakon

11 May 07 at 7:07 am

Any thoughts on how to make this query work with dates? I have a scenario where I need to find overlapping or gaps between ranges of dates. For example:

start_date end_date ---------- ---------- 01/01/2007 01/31/2007 02/15/2007 02/28/2007 04/01/2007 04/20/2007 04/15/2007 04/30/2007

An ideal solution would return to me a recordset for the gaps:

gap_start gap_end --------- -------- 02/01/2007 02/14/2007 03/01/2007 03/31/2007

and another query might return any overlaps:

gap_start gap_end --------- --------- 04/15/2007 04/20/2007

With a little bit of preliminary work, it looks like this algorithm could be adapted as long as there was a way to use dates in the sequence table.

Any thoughts or ideas?

Thanks,
Tim

Tim

16 May 07 at 4:47 pm
It will depend on your system, but as long as your RDBMS supports adding and subtracting dates, certainly. I referred to getting the ‘next’ value in the sequence — Given 2007-05-16, next is the 17th. In all systems I’ve worked with there is something equivalent to ADDATE() or similar.

Xaprb

16 May 07 at 6:45 pm
Thanks so much for this article! I was dreading having to make a table of legal values and do an exclusion join on them – I feel like that would be such a hack! Your solution is much more elegant.

I was able to adapt your idea to suit my purposes, but it got a little ugly. The data model I’m working with contains entries for inventory reports, and I wanted to figure out the dates for the ones we’re missing, which I could cross-reference with the shift schedules to figure out who to yell at. However, inventory entries are done at the beginning of the day AND at the end, so it’s not as simple as comparing dates. I want to know if we’re missing the opening entry on a given date, if we’re missing the closing entry on that date, or both. Figuring out both the date and the is_opening flag was a pain. Here’s an example dataset:

date | is_opening
———— ————
2007-05-01 | t
2007-05-01 | f
2007-05-02 | t
2007-05-02 | f
2007-05-03 | f
2007-05-05 | t
2007-05-05 | f
2007-05-06 | t
2007-05-06 | f
2007-05-07 | f
2007-05-08 | t
2007-05-08 | f
2007-05-09 | t
2007-05-09 | f
2007-05-10 | f
2007-05-11 | t
2007-05-11 | f
2007-05-12 | t
2007-05-12 | f

The answer I’m looking for is:

start | start_is_opening | stop | stop_is_opening
———— —————— ———— —————–
2007-05-03 | t | 2007-05-03 | t
2007-05-04 | t | 2007-05-04 | f
2007-05-07 | t | 2007-05-07 | t
2007-05-10 | t | 2007-05-10 | t

The query ended up being pretty horrendous, but rather straightforward if you think about it:

SELECT
CASE loj.start_is_opening
WHEN ‘t’ THEN loj.start
WHEN ‘f’ THEN DATE(loj.start INTERVAL ’1 day’)
END AS start,
NOT loj.start_is_opening AS start_is_opening,
CASE roj.stop_is_opening
WHEN ‘t’ THEN DATE(roj.stop – INTERVAL ’1 day’)
WHEN ‘f’ THEN roj.stop
END AS stop,
NOT roj.stop_is_opening AS stop_is_opening
FROM
(
SELECT ql.date AS start, ql.is_opening AS start_is_opening
FROM quisnomicon ql
WHERE NOT EXISTS (
SELECT qr.date
FROM quisnomicon qr
WHERE (
ql.date = qr.date – INTERVAL ’1 day’
AND ql.is_opening = ‘f’
AND qr.is_opening = ‘t’
)
OR
(
ql.date = qr.date
AND ql.is_opening = ‘t’
AND qr.is_opening = ‘f’
)
)
) AS loj,
(
SELECT qr.date AS stop, qr.is_opening AS stop_is_opening
FROM quisnomicon qr
WHERE NOT EXISTS (
SELECT ql.date
FROM quisnomicon ql
WHERE (
ql.date = qr.date – INTERVAL ’1 day’
AND ql.is_opening = ‘f’
AND qr.is_opening = ‘t’
)
OR
(
ql.date = qr.date
AND ql.is_opening = ‘t’
AND qr.is_opening = ‘f’
)
)
) AS roj
WHERE loj.start = (
SELECT MAX(q.date)
FROM quisnomicon q
WHERE q.date loj.start
);

Postgres reports that the query’s cost=5660.50..17660.63. Is there some clever way of making it less expensive? Am I doing something that’s obviously wrong?

Thanks again for the basic idea on how to accomplish this query in the first place!

Jack

1 Jul 07 at 6:34 am
I cant get this to work :(
It adds the values find, but when I tired and run:

**********************************
SELECT START ,
STOP FROM (

SELECT m.id 1 AS
START , (

SELECT min( id ) -1
FROM sequence AS x
WHERE x.id > m.id
) AS
STOP
FROM sequence AS m
LEFT OUTER JOIN sequence AS r ON m.id = r.id -1
WHERE r.id IS NULL
) AS x
WHERE STOP IS NOT NULL ;
**********************************

I get this:
**********************************
SQL query: Edit

SHOW KEYS FROM

MySQL said: Documentation
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1
**********************************

Any ideas?

Some1

13 Jul 07 at 5:05 am
You have to use a version of MySQL that supports subqueries. What version are you using? My guess is 4.0.x.

Xaprb

13 Jul 07 at 4:54 pm
Hello,

Your query works very well, and I am very pleased with it, however. My database has some entries in it’s identity column with characters entires… (i.e. 1, 2, 3, 3a, 3b, 4 …..).
And I was wondering if there was a way to execute this query with those entries present, either by ignoring them, or what not.

Thanks,

Whalen

Whalen

25 Jul 07 at 3:22 pm
I will appreciate it very much if you could suggest how to look for sequence patterns with SQL.

Thanks,
Joris

Joris

14 Sep 07 at 12:14 pm
hello
I have a problem ,
my table name is cbl ,it contains the following entries

seq country
1———-USA
2———-UK
2———-USA
2———-Canada
5———-USA
4———-UK
6———-Canada

i want the result to Find the missing sequence as the following based on the table

USA 3,4
UK 1,3
Canada 1,3,4,5

i am using sql builder which is used in access

please do help out , I have been trying to figure it out for some time now

thanks in advance

john

6 Nov 07 at 8:25 am
the fastest and more staight straightforward apporach is using a generic number table (pre-populate from 1 to 10 Million, for example)
Then left join your table with the number table to find missing rows.
You can fully benifit the index on 2 tables.
Especially for large tales, the merge join will only scan each table just one time.

– david

david wei

15 Nov 07 at 3:56 pm
What if I had a table like Sara’s example (from above)…

a1 a2 a3
——————
1 ddd aaa
1 ooo bbb
4 vvv aaa
5 qqq bbb
7 hhh bbb
6 www aaa

and I wanted to get the missing numbers in column a1 for each value in a3, like…

—– ——- ——
| a3 | start | stop |
—– ——- ——
| aaa | 2 | 3 |
| aaa | 5 | 5 |
| bbb | 2 | 4 |
| bbb | 6 | 6 |
—– ——- ——

Thank you for any help!

ryan

3 Dec 07 at 10:20 pm

One of your columns is extra, so I’ll just remove it to clarify the query:

select * from sara;
+----+------+
| a1 | a2   |
+----+------+
|  1 | aaa  | 
|  2 | bbb  | 
|  4 | aaa  | 
|  5 | bbb  | 
|  7 | bbb  | 
|  6 | aaa  | 
+----+------+

select a2, start, stop from(
   select m.a2, m.a1 + 1 as start,
   (select min(a1)-1 from sara as x where x.a1 > m.a1 and x.a2 = m.a2) as stop
   from sara as m
      left outer join sara as r on m.a2 = r.a2 and m.a1 = r.a1 - 1
   where r.a1 is null
   group by m.a2, start
) as x
where stop is not null;

+------+-------+------+
| a2   | start | stop |
+------+-------+------+
| aaa  |     2 |    3 | 
| aaa  |     5 |    5 | 
| bbb  |     3 |    4 | 
| bbb  |     6 |    6 | 
+------+-------+------+

Xaprb

4 Dec 07 at 9:35 am

Using the example above:

select l.id 1 as start, min(fr.id) – 1 as stop
from sequence as l
left outer join sequence as r on l.id = r.id – 1
left outer join sequence as fr on l.id

Cody

14 Dec 07 at 11:38 am
Awesome. Thanks so much for publishing this method of checking for missing rows.

Bobby Breaux

16 Feb 08 at 3:04 pm
select l.id + 1 as start, min(fr.id) – 1 as stop
from sequence as l
left outer join sequence as r on l.id = r.id – 1
left outer join sequence as fr on l.id

Gabor Kiss

10 Mar 08 at 6:08 am
That is a nice solution. However I cannot understand
the semantics of “min(fr.id)”. There is no GROUP BY
constriction here. I replaced “MIN(fr.id)” with
simple “fr.id” and I got the same answer.
What did I miss? :-)

Gabor Kiss

10 Mar 08 at 6:09 am
Fantastic! Thanks for putting in the effort to share your work.

John

28 Mar 08 at 5:11 pm
I really enjoyed this article on “How to find missing values in a sequence with SQL”. I have a related problem involving finding a missing period of time from a datetime field. For example, I might need to go through a week and find all the times in a variable work day (maybe 8 hours one day and 10 the next) where there is a gap of 5 minutes or more, and keep a sum of the missed time for the day. For example, if two lines were 1/1/2008 01:00:00 AM and 1/1/2008 01:11:00 AM, then that would be a total of 11 minutes that would be added to the total for the day. I want to ignore any gaps less than 5 minutes. Thank you for any help you can give me on this!

Rob

18 Apr 08 at 5:04 pm
Thank you! Very helpful in solving a skip in a primary key that used to be auto-assigned.

Keith

9 May 08 at 2:19 pm
Greate soution! However I cannot find the gap if it starts with 1. How to solve this?

Daniel

27 May 08 at 8:53 am
Hey this is a great article… but my problem is more complicated
I want to find the missing sequences for a 3 column join like
Col1 Col2 Col3
1 1 1
1 2 2
1 2 4
2 2 3
2 2 5
3 3 6
3 5 8
And so on… I need to find out all unused permutations in this series.. A query to solve this will be of great help.. Thanks in advance

Jay

30 May 08 at 1:49 pm
if i want to find gap for example a01, a03, a05?

charlene

20 Aug 08 at 1:37 am
i have a table

FROM TO
1 5
6 10
15 20
29 30

i need a query which can give me missing range as

11-14

thanks in advance

arvind

24 Sep 08 at 2:05 am
This is a great article. It wasn’t exactly what it needed for my particular problem but it definitely got me in the right direction. Thanks for sharing your thoughts and wisdom.

Michael McSorely

10 Oct 08 at 2:54 am
Wonderful. Thank you very much. This article helped a lot in understanding how to solve the sql problem I was working on for days.

Satya

18 Oct 08 at 5:07 am
Great stuff. I just wrote a check writing app, and need to find gaps in check numbers. Works like a charm.

Thanks much.

RJB

18 Nov 08 at 4:24 pm
How to get?????????????????????????

ID
—
5
11
12
13
14

Serg

30 Nov 08 at 10:12 am
on the firist example how to find these values
5, 11, 12, 13, 14,

YASSER

3 Dec 08 at 3:19 am
Exclusion join against a numbers table. Links are in the article.

Xaprb

3 Dec 08 at 9:24 am
Thank you for the “Finding duplicate and missing numbers”. It was a big help.

happiness

16 Dec 08 at 7:50 am
hi Xaprb,

it’s nice to get missing range. but sometime i just need to get like this:

start end
1 4
6 10
16 20

what should i do?

ehm

16 Dec 08 at 12:48 pm
Thank you. Exactly what I needed.

Schall

15 Jan 09 at 6:07 pm
i need all missing number query with out left/inner/outter join as
reall sequence
2,5,6,9,14,15,18,20

expected missing result
0,1,3,4,7,8,10,11,12,13,14,16,17,19

please help me to write this query

arun

4 Feb 09 at 8:04 am
Hi Master Xaprb,

If the holes are not present, how can i deduce that it is null?

Thank you very much.. I would really dream to be your apprentice.. :)

Miguel

23 Feb 09 at 3:26 am
Under assumption that there are holes among ID (in sequence) of squares, find the minimal and maximal “free” IDs in a range between available maximal and minimal IDs
– The query have satisfied this but not the second condition
If holes are not present, deduce NULL
can you help me with this,

thank you very much…

Miguel

23 Feb 09 at 5:22 am
Miguel, I’m sorry but I don’t understand the question. Please give an example sequence of numbers and show the results you would want to get.

Xaprb

23 Feb 09 at 11:08 pm
Thanks for your reply, apologies for the confusion
here is an Example: For the sequence of square IDs 1,2,5,7, the result must be 3 and 6,

Miguel

24 Feb 09 at 5:50 am
I just need to return the Upper and the lower range.

Miguel

24 Feb 09 at 6:14 am
So you can do something like

SELECT min(col), max(col) FROM (
… query as shown in this article
) as x;

Xaprb

24 Feb 09 at 8:50 am
Thank your very much. apolodies as I am just begining SQL DML thanks

Miguel

24 Feb 09 at 9:11 pm
say you have a data
point date outpu
1 2/1 1
1 3/11 7
1 3/16 3
1 4/21 11
2 1/3 1
2 6/2 11
3 5/1 7
3 5/6 1

OUTPUT
point date profit
1 3/16 1
2 6/2 2
3 5/6 -2

The query have to stop when the output reaches 10 and return the whole column,

also if it did not reach 10 we are also require to return the value.

I would appreciate if you can help me figure this one out thank you very much..

Mikko

25 Feb 09 at 12:23 am
reposting for better view
say you have a data
point date outpu
1 | 2/1 | 1
1 | 3/11 | 7
1 | 3/16 | 3
1 | 4/21 | 11
2 | 1/3 | 1
2 | 6/2 | 11
3 | 5/1 | 7
3 | 5/6 | 1

OUTPUT
point date profit
1 3/16 1
2 6/2 2
3 5/6 -2

The query have to stop when the output reaches 10 and return the whole column,

also if it did not reach 10 we are also require to return the value.

I would appreciate if you can help me figure this one out thank you very much..

Mikko

25 Feb 09 at 12:23 am

Mikko

25 Feb 09 at 12:25 am
[...] http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/ [...]

How to find missing values in a sequence with SQL

25 Feb 09 at 3:00 am
I was browsing your site all day.. older entries newer entries, and read a lot of good stuff, Im just wodering if you have a page which has all the topics you have created on a list, so I can just browse through that page..
Thanks.

Mikko

25 Feb 09 at 8:39 pm
To find the missing numbers in any given sequence, try the code below. The code is applied on EMP table
from Scott’s schema.
———————————————–
declare
cursor gaps_c is
select before_gap,after_gap from
(WITH aquery AS
(SELECT pk_column after_gap,
LAG(empno,1,0) OVER (ORDER BY empno) before_gap
FROM emp)
SELECT
before_gap, after_gap
FROM
aquery
WHERE
before_gap != 0
AND
after_gap – before_gap > 1
ORDER BY
before_gap
) gaps;
begin
for gaps_rec in gaps_c loop
for i in 1..gaps_rec.after_gap-gaps_rec.before_gap-1 loop
gaps_rec.before_gap:= gaps_rec.before_gap+1;
dbms_output.put_line(gaps_rec.before_gap);
end loop;
end loop;
end;
———————————————–
Pretty neat eah.

Mohammed Kassab

30 Mar 09 at 7:01 am
Is this possible :

I’ve got the following list of values :

A01
A02
A03
A04
A06
A07
A08
A09
A10
A11
A12
A13
A15

From the above I need to capture that A05 & A14 is missing?

Iain

30 Apr 09 at 5:20 am
Hi Ian,

To solve your problem we will assume that we have a table named “TEST” composed of one column named “seq”, the table will accommodate the data u just provided. Kindly check the table description as shown …

desc test

Name Null Type
—— ——– ———–
SEQ VARCHAR2(5)
1 rows selected

If we issue …
select * from test;
We result with …

SEQ
—
A01
A02
A03
A04
A06
A07
A08
A09
A10
A11
A12
A13
A15

The PL\SQL code to produce the desired output is shown below … The key here is extract the integer part from the sequence and start working on it separately. Just as simple as that, here we go …

set serveroutput on
declare
k number(6) := 1; — counter to count the number of unused serials [i just used k for Kassab]
cursor gaps_c is
select before_gap,after_gap from
( WITH gap_query AS
( SELECT to_number(substr(seq,2)) after_gap,
LAG(to_number(substr(seq,2)),1,0) OVER (ORDER BY to_number(substr(seq,2))) before_gap
FROM test)
SELECT before_gap, after_gap
FROM gap_query
WHERE before_gap != 0
AND after_gap – before_gap > 1
ORDER BY before_gap
) gaps;
begin
for gaps_rec in gaps_c loop
for i in 1..gaps_rec.after_gap-gaps_rec.before_gap-1 loop
k := k + 1;
gaps_rec.before_gap:= gaps_rec.before_gap+1;
dbms_output.put_line(‘A’||to_char(gaps_rec.before_gap,’fm09′));
end loop;
end loop;
dbms_output.put_line(‘———————-’);
dbms_output.put_line(to_char(k-1,’fm999,999′)||’ UNUSED SERIALS’);
dbms_output.put_line(‘———————-’);
end;

Our output will show the missing values 5 and 14 as explained below adding to that the number of missing serials …

A05
A14
———————-
2 UNUSED SERIALS
———————-

… hope that helps ;)

Please don’t hesitate to contact me on kassab_m@amig.com.eg in case u have any doubts.

Take care.

Regards,

Mohammed Kassab
Deputy IT Manager

Kassab

3 May 09 at 2:11 am
Hi Iain,

To solve your problem we will assume that we have a table named “TEST” composed of one column named “seq”, the table will accommodate the data u just provided. Kindly check the table description as shown …

desc test

Name Null Type
—— ——– ———–
SEQ VARCHAR2(5)
1 rows selected

If we issue …
select * from test;
We result with …

SEQ
—
A01
A02
A03
A04
A06
A07
A08
A09
A10
A11
A12
A13
A15

The PL\SQL code to produce the desired output is shown below … The key here is extract the integer part from the sequence and start working on it separately. Just as simple as that, here we go …

set serveroutput on
declare
k number(6) := 1; — counter to count the number of unused serials [i just used k for Kassab]
cursor gaps_c is
select before_gap,after_gap from
( WITH gap_query AS
( SELECT to_number(substr(seq,2)) after_gap,
LAG(to_number(substr(seq,2)),1,0) OVER (ORDER BY to_number(substr(seq,2))) before_gap
FROM test)
SELECT before_gap, after_gap
FROM gap_query
WHERE before_gap != 0
AND after_gap – before_gap > 1
ORDER BY before_gap
) gaps;
begin
for gaps_rec in gaps_c loop
for i in 1..gaps_rec.after_gap-gaps_rec.before_gap-1 loop
k := k + 1;
gaps_rec.before_gap:= gaps_rec.before_gap+1;
dbms_output.put_line(‘A’||to_char(gaps_rec.before_gap,’fm09′));
end loop;
end loop;
dbms_output.put_line(‘———————-’);
dbms_output.put_line(to_char(k-1,’fm999,999′)||’ UNUSED SERIALS’);
dbms_output.put_line(‘———————-’);
end;

Our output will show the missing values 5 and 14 as explained below adding to that the number of missing serials …

A05
A14
———————-
2 UNUSED SERIALS
———————-

… hope that helps ;)

Please don’t hesitate to contact me on kassab_m@amig.com.eg in case u have any doubts.

Take care.

Regards,

Mohammed Kassab
Deputy IT Manager

Mohammed Kassab

3 May 09 at 2:15 am
Hi Mohammed

Thanks for the solution, works great! Quick question (again!), is there any way to achieve this without using cursors? Not a fan of cursor, have caused problems in the past regarding performance.

Regards
Iain

Iain

5 May 09 at 9:03 am
Hi Ian,

I prefer using curosrs, but I will try to find another alternative. I’ll keep u posted.

Regards,

K

Mohammed Kassab

6 May 09 at 7:27 am
Really a very helpful….
Thanks

Kapil

23 Jun 09 at 10:35 pm
How would I do the same as above based on a DateTime field as the unique entry.

For example I have a table as follows

RecordTime ……………….. Tension
2009-01-01 00:00:00.000 ……. 1
2009-01-01 00:00:01.000 ……. -8888
2009-01-01 00:00:02.000 ……. 3
2009-01-01 00:00:03.000 ……. 4
2009-01-01 00:00:04.000 ……. 2
2009-01-01 00:00:05.000 ……. 1
2009-01-01 00:00:06.000 ……. -8888
2009-01-01 00:00:07.000 ……. -8888
2009-01-01 00:00:08.000 ……. 4
2009-01-01 00:00:09.000 ……. 2
2009-01-01 00:00:10.000 ……. 1
2009-01-01 00:00:11.000 ……. -8888
2009-01-01 00:00:12.000 ……. -8888
2009-01-01 00:00:13.000 ……. -8888
2009-01-01 00:00:14.000 ……. -8888
2009-01-01 00:00:15.000 ……. 1
2009-01-01 00:00:16.000 ……. -8888
2009-01-01 00:00:17.000 ……. 3
2009-01-01 00:00:18.000 ……. 4
2009-01-01 00:00:19.000 ……. 2

The above Table is defined as RecordTime DATETIME, Tesnion INTEGER

I would like to be able to return the ranges of data that are equal to -8888, so the returned data would be as follows

2009-01-01 00:00:01.000 …. 2009-01-01 00:00:01.000
2009-01-01 00:00:06.000 …. 2009-01-01 00:00:07.000
2009-01-01 00:00:11.000 …. 2009-01-01 00:00:14.000
2009-01-01 00:00:16.000 …. 2009-01-01 00:00:16.000

I want to be able to do this in SQLite if that is at all possible, rather than a full blown SQL package.

Any assistance would be greatly appreciated.

Andrew Lindsay

19 Jul 09 at 9:26 am
Thanks, saved me hours of head scratching

H Speight

19 Oct 09 at 7:03 pm
I tell people all the time that you’re one correct google search away from the answer to almost any question. This proves it. I was trying to get this done many different ways on my own without much success. What a time saver. Thanks.

Tim

8 Dec 09 at 6:01 pm
Hi,

How to find all missing sequencing no like
your no is
1,2,3,4,6,7,8,9,10,15,16,17,18,19,20

required output:
5, 11, 12, 13, 14

Thanks and regards;
Manmohan

Manmohan

23 Dec 09 at 9:15 am
This article was incredibly helpful. Thanks for posting it.

Amanda

26 Jan 10 at 10:10 pm
To solve your problem we will assume that we have a table named “XPARB” composed of one column named “seq”,
the table will accommodate the data u just provided. Kindly check the table description as shown …

desc test

desc xparb;
Name Null Type
—– —- —–
SEQ NUMBER(5)

If we issue …
select * from XPARB;
We result with …

SEQ
—
1
2
3
4
6
7
8
9
10
15
16
17
18
19
20

The PL\SQL code to produce the desired output is shown below…

set serveroutput on
declare
k number(6) := 1; — counter to count the number of unused serials [i just used k for Kassab]
cursor crs1 is
select seq,LAG(seq, 1, 0) OVER (ORDER BY seq) AS seq_prev
from xparb; — cursor to fetch all sequences in xparb table
cnt number; — total count of sequences in our e.g. (15) = 1,2,3,4,6,7,8,9,10,15,16,17,18,19,20
v_seq1 number(5);
v_seq2 number(5);
v_diff number(5);
begin
select count(*) into cnt from xparb;
open crs1;
loop
fetch crs1 into v_seq1, v_seq2;
if v_seq1 – v_seq2 1 then
–dbms_output.put_line(v_seq1-1||’,'|| v_seq2);
v_diff := (v_seq1-1)-v_seq2;
–dbms_output.put_line(v_diff);
for i in 1..v_diff loop
dbms_output.put_line(v_seq2+i);
end loop;
end if;
exit when crs1%notfound or crs1%rowcount >= cnt;
end loop;
close crs1;
end;

Hope that helps.

Regards,
Mo’ Kassab

Mohammed Kassab

27 Jan 10 at 3:31 am
Hello,

There is one question that no one has answered from my pont of view.
Given the series in a column 2, 4, 5, 6, 8
Return 1,3,7

Is there any query that can achieve this?

2 people asked this already but they didn’t receive an answer.
Thanks in advance.

Para

10 Feb 10 at 5:51 pm
Dear Para,
———————————–
I created a table named [xparb] containing 5 rows [2,4,5,6,8]. Run the code below:
———————————–
declare
k number(6) := 1; /* counter to count the number of unused serials [i just used k for Kassab] */
cursor crs1 is
select seq,LAG(seq, 1, 0) OVER (ORDER BY seq) AS seq_prev
from xparb; /* cursor to fetch all sequences in xparb table */
cnt number; /* total count of sequences in our e.g. = 2,4,5,6,8 */
v_seq1 number(5);
v_seq2 number(5);
v_diff number(5);
begin
select count(*) into cnt from xparb;
open crs1;
loop
fetch crs1 into v_seq1, v_seq2;
if v_seq1 – v_seq2 1 then
v_diff := (v_seq1-1)-v_seq2;
for i in 1..v_diff loop
dbms_output.put_line(v_seq2+i);
end loop;
end if;
exit when crs1%notfound or crs1%rowcount >= cnt;
end loop;
close crs1;
end;
———————————–
and you’ll end up with the result below:
1
3
7
———————————–
Hope this is convenient enough.

Regards,
Mo’ Kassab

Mohammed Kassab

11 Feb 10 at 3:35 am
It occurred to me the sum of sequential numbers in the id column would equal the sum of sequential numbers 1…n , which is represented by n(n+1)/2, as long as none are missing. Thus to find the first occurrence,

SELECT id-1,
( SELECT SUM(t2.id) FROM sequence t2 WHERE t2.id<=t1.id ) sum1,
id*(id+1)/2 sum2
FROM sequence t1
HAVING sum1!=sum2
LIMIT 1

without the limit 1, the rest of the results are nonsense because the running total gets messed up. id-1 represents the missing number … for example id=6 gives you 16 and 21, this is because 5 is missing.

i tried this on a larger dataset and it was slow just like the original solution. 4+ years ago and we're still talking about this, haha.

amac44

29 Mar 10 at 3:10 pm
Or using row number (again only finds first occurrence),

SET @i = 0;
SELECT id, @i:=@i+1 myrow_no FROM seq HAVING id>myrow_no limit 1

amac44

29 Mar 10 at 3:24 pm
OK you’ll think I’m a dork, but here’s some PHP+MySQL to grab all the entries using the row number technique. This worked faster for me than the original solution. The particulars are using tables from Expression Engine CMS, but you can tweak the table and field names as desired. I’m sure this could be improved, but it sure saved me some time. Basically works by adding any “found” IDs by UNION, resorting and requerying.

<?

$db = mysql_connect("localhost", "user", "password") or die ( mysql_error() );
mysql_select_db("mydb",$db);

$sql_counter = "SET @i = 111110;"; //arbitrary starting point
$q = mysql_query($sql_counter) or die ( "ERR1: ".mysql_error() );

$sql1 = <<<EOT

SELECT d2.field_id_14, @i:=@i+1 myrow_no
FROM (
SELECT d.field_id_14
FROM exp_weblog_data d
WHERE weblog_id=5

EOT;

$sql2 = <<myrow_no
LIMIT 1
EOT;

$union = “”;
$sql_this = $sql1 . $sql2;

$q = mysql_query($sql_this) or die ( “ERR2: $sql_this : “.mysql_error() );
while ($r=mysql_fetch_array($q)) {
echo($r[0].’ ‘.$r[1]).”\n”;
$union .= ” UNION SELECT “.($r['myrow_no']-1);
$q = mysql_query($sql_counter) or die ( “ERR1: “.mysql_error() );
$sql_this = $sql1 . $union . $sql2;
$q = mysql_query($sql_this) or die ( “ERR2: $sql_this : “.mysql_error() );
echo (mysql_num_rows($q));
}

mysql_close($db);

echo $sql_this;

?>

###

amac44

30 Mar 10 at 4:08 pm
Great job dude

Shailendra

8 Jun 10 at 1:12 am
I have a table t1 in which there is a numeric column sr_num.
In that table I want to see missing numbers in the range 30 to 60
and fortunately the particular data is not present then I want output as:
start stop
30 60

Deepak

30 Jul 10 at 2:32 am
Hi,

I have a table with 7 records as below

sl.no name

2 Jogi
3 Louis
5 Patel
6 san
the missing
What I can do to find out the missing records with sl.no 1,4,7. I tried but I can’t able to get the sl.no 1 and 7.So anyone please help me to get the solution.

Thanks in advance

Sujatha Thangaraj

16 Aug 10 at 2:46 am
Could left join your table with a column of sequential numbers, I don’t know why I didn’t think of that before (just using temp tables for kicks, you can use real tables):

CREATE TEMPORARY TABLE t1 (`sl.no` INT, `name` TINYTEXT);
INSERT INTO t1 VALUES (2,’Jogi’), (3,’Louis’), (5,’Patel’), (6,’san’);
CREATE TEMPORARY TABLE t2 (n INT);
INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7);

SELECT n FROM t2 LEFT JOIN t1 ON `sl.no`=n WHERE `sl.no` IS NULL;

n
1
4
7

amac44

16 Aug 10 at 12:30 pm
thanks. that was exactly what i needed!

Spencer

17 Aug 10 at 12:20 pm
Hi,

The left join is working but in my database I need too insert a sequence of numbers from 1 to 200.can anyone tel me how to insert those values?

Thanks in advance..

Sujatha Thangaraj

18 Aug 10 at 7:54 am
You need to use a “numbers table.” See http://www.xaprb.com/blog/2005/12/07/the-integers-table/

Xaprb

18 Aug 10 at 10:16 am
That’s a nifty technique. I’ve done it by incrementing a counter with an arbitrary query with select @i:=@i+1 but this is cleaner. Note of course you can use LIMIT and WHERE to specify ranges etc. if need be

amac44

19 Aug 10 at 12:21 pm
Hi,
too bad that the very first comment
“Works well, except that (as expected, I assume) seq 2,3,4 returns 5, where someone (i.e. me) would actually want 1.”
is still unanswered.
I am also currently looking for a solution to this.

Thanks
Michael

MiSter

21 Jan 11 at 7:19 am
Thank you so much Xaprb.You make our project easy..ummmmwwwwhaaa…

chavika

13 Mar 11 at 2:08 pm
I am trying to do a similar thing in MS Access. I have generated a list of used numbers from another process and I want to make a list for the end user of available numbers based on the list of taken numbers.
I have a field STAND_NO in UsedStandNumberList table which contains:
1,2,3,4,5,6,7,8,10,11,13,14,15,16,17,19
It comes across as text so I used VAL(STAND_NO) to work with the number. I am able to find the list of missing numbers, plus the next after the largest value:
9,12,18,20
I used the following:
SELECT VAL(STAND_NO)+1 AS Available
FROM UsedStandNumberList AS a
WHERE NOT EXISTS
( SELECT 0
FROM UsedStandNumberList b
WHERE VAL(b.STAND_NO) = VAL(a.STAND_NO) + 1);

What I would really like is to have a list of available numbers from what is “missing” from the taken list, plus sequence up beyond the max of the last taken number to a specified upper limit, let’s just say 25. So the result in the Available column would be:
9,12,18,20,21,22,23,24,25
Is there any way to modify the query above to keep incrementing past the MAX value to a specified limit?

This is a great site, by the way, and I’ve already learned lots!

Christal

5 May 11 at 7:08 pm
I realized when I tested on a different sample that my query above does not get at what I want exactly if there are gaps greater than one between numbers. So if my sample had taken stand numbers
STAND_NO
1,2,3,5,6,7,10,11,12,14,18
my result is:
4,8,13,15,19
when I want it to be:
4,8,9,13,15,16,17,19

(really 4,8,9,13,15,16,17,19,20,21,22,23,24,25)

Christal

5 May 11 at 7:45 pm
Dear Christal,

I created a table called Xaprb with a single column [seq] with the data you provided above.
The rows in the table are [1,2,3,5,6,7,10,11,12,14,18]

I then ran the following block:
declare
cursor seq_crs is
select seq from xaprb order by seq;
x number; — start with minimum number
begin
select min(seq) into x from xaprb;
for rec in seq_crs loop
while x <= rec.seq loop
if x rec.seq then
dbms_output.put_line(x);
end if;
x := x+1;
end loop;
end loop;
end;

my result came out to be:
anonymous block completed
4
8
9
13
15
16
17

This block pin points the missing sequence between “1″ and “18″ since they represent the lower and upper boundries of the list.

Hope that solved your problem.

Regards,
Kassab

Mo Kassab

7 May 11 at 2:07 am
Thanks a bunch!

Anon

17 May 11 at 9:55 am
Excellent piece of work! Thanks!

Wessel

8 Jun 11 at 6:40 pm
You’re a monster

SQL Noob

2 Jul 11 at 1:13 pm
very nice sql. good job, i search the internet for days trying to find a solution.

i have adapted it to work with dates. it works ok if all the dates are related to 1 object (say 1 villa). how can i make it work when by bookings table contains many villas and booktype( individual or group)

see table and sql below

CREATE TABLE IF NOT EXISTS `books` (
`id` int(11) NOT NULL auto_increment,
`booktype` int(1) NOT NULL default ’0′,
`startdt` date NOT NULL default ’0000-00-00′,
`enddt` date NOT NULL default ’0000-00-00′,
`villaid` int(11) NOT NULL default ’0′
);

select DATE_ADD(l.enddt,INTERVAL 1 day) as start, DATE_sub(fr.startdt,INTERVAL 1 day) as stop,
from books as l
left outer join books as r on l.enddt = DATE_sub(r.enddt,INTERVAL 1 day)
left outer join books as fr on l.enddt < fr.startdt
where (r.enddt is null and fr.startdt is not null)
group by l.enddt, r.enddt;

as i say the above works great if only 1 villa is in books with many booking dates. i want it to work regardless of how many villas have bookings.

CT

18 Jul 11 at 5:09 pm
CT,

Could you please send me a sample data along with the desired output. I don’t fully understand your request.

Regards,
Kassab

Mo' Kassab

20 Jul 11 at 2:22 am
OMG, awesome!!!!!

Kain

6 Sep 11 at 8:52 pm
Is this possible?

I’ve got the following list of values :
table name fast and columne name code

5G0001
5G0002
5G0003
5G0004
5G0005
5G0006
5G0007
5G0008
5G0009
5G0011
5G0012
5G0013
5G0014
5G0018
5G0020
5G0021

From the above I need to capture that 5g0010, 5G0015, 5G0016 & 5G0017 is missing how i can find the missing number.
plz give me a particular query.

LALIT

7 Oct 11 at 7:20 am
Commenters,

I am going to stop approving further comments that say “here is my data, give me the query that does what I need.” There are more than enough examples already that illustrate the principles my post teaches. Think, study, learn, apply, and become independently able to solve your own problems.

Xaprb

7 Oct 11 at 10:06 pm
Dear Lalit,

Using the above code posted earlier on 7-May, I have made some modifications to tune in with your requirements.

Check the code below:

declare
cursor seq_crs is
select to_number(substr(code,-2)) as code from fast order by code;
x number; /*start with minimum number*/
begin
select min(to_number(substr(code,-2))) into x from fast;
for rec in seq_crs loop
while x <= rec.code loop
if x rec.code then
dbms_output.put_line(’5G’||to_char(x,’fm0999′));
end if;
x := x+1;
end loop;
end loop;
end;

my result came out to be:
anonymous block completed
5G0010
5G0015
5G0016
5G0017
5G0019

Hope that solved your problem.

Regards,
Kassab

Mo' Kassab

8 Oct 11 at 1:21 am
Dear Lalit,

Using the above code I posted earlier on 7-May, I have made some modifications to tune in with your requirements.

Check the code below:

declare
cursor seq_crs is
select to_number(substr(code,-2)) as code from fast order by code;
x number; /*start with minimum number*/
begin
select min(to_number(substr(code,-2))) into x from fast;
for rec in seq_crs loop
while x <= rec.code loop
if x rec.code then
dbms_output.put_line(’5G’||to_char(x,’fm0999?));
end if;
x := x+1;
end loop;
end loop;
end;

my result came out to be:
anonymous block completed
5G0010
5G0015
5G0016
5G0017
5G0019

Hope that solved your problem.

Regards,
Kassab

Mo' Kassab

8 Oct 11 at 1:22 am
Dear Lalit,

Using the above code I posted earlier on 7-May, I have made some modifications to tune in with your requirements.

Check the code below:

declare
cursor seq_crs is
select to_number(substr(code,-2)) as code from fast order by code;
x number; /*start with minimum number*/
begin
select min(to_number(substr(code,-2))) into x from fast;
for rec in seq_crs loop
while x <= rec.code loop
if x != rec.code then
/* if x doesn't equal rec.code then …*/
dbms_output.put_line(’5G’||to_char(x,’fm0999?));
end if;
x := x+1;
end loop;
end loop;
end;

my result came out to be:
anonymous block completed
5G0010
5G0015
5G0016
5G0017
5G0019

Hope that solved your problem.

Regards,
Kassab

Mo' Kassab

8 Oct 11 at 1:24 am
Does anybody know how to alias the subquery solution correctly so that it runs in Teradata?

select start, stop from (
select m.id + 1 as start,
(select min(id) – 1 from sequence as x where x.id > m.id) as stop
from sequence as m
left outer join sequence as r on m.id = r.id – 1
where r.id is null
) as x
where stop is not null;

Michael

15 Nov 11 at 5:30 pm
lovely, years after and still a great post. Good job thank you

steven

21 Jan 12 at 2:28 pm
Thank you Steven.
Regards,
Mo’ Kassab

Mo' Kassab

23 Jan 12 at 2:56 am
Thanks for a helpful blog entry, helped a lot! :)

davidp

20 Feb 12 at 5:23 am
Glad that helped you out David.
Mo’ Kassab

… as for Michael … can u plz tell me what problem is so I might help u out.
thanks …

Mo' Kassab

26 Feb 12 at 2:40 am
Hello,

I was wondering if I can get some assistance on an issue I have similar to this. I used the ‘Find ranges of missing values with subqueries’ example and I have it working but I ran into an issue.

An example of my data set:

ID TicketID
1 00001
2 00002
3 00003
4 00005
5 00006
6 00007
7 00009
8 00010
9 00001
10 00002
11 00003
12 00004
13 00005
14 00006
15 00007
16 00008
17 00009
18 00010

My Ticket values restart to one after a certain point. When I run the query I have it would not have any errors because of the latter rows. But I need a query that would be able to determine the missed values for TicketID: 4 and 8.

Would this be possible?

Thanks for the help!

Michael W

27 Apr 12 at 9:59 am
Dear Michael W,
I assumed that I have a table named “tickets” containing a single column named “ticket_id” of type number. I inserted the your data with the same order and ran the below pl/sql code and my output came out to be …
0004
0008
——————————————-
declare
cursor seq_crs is
select ticket_id as ticket_id from tickets;
x number;
begin
select min(ticket_id) into x from tickets;
for rec in seq_crs loop
while x <= rec.ticket_id loop
if x != rec.ticket_id then
dbms_output.put_line(to_char(x,'fm0999'));
end if;
x := x+1;
end loop;
end loop;
end;
——————————————-
Thanks & Regards,
Mo' Kassab

Mo' Kassab

30 Apr 12 at 2:53 am
Mo’Kassab:

I attempted to use this but I believe it is in Oracle SQL. Any suggestions for T-SQL?

Thanks,
Michael W.

Michael W

30 Apr 12 at 2:04 pm
0,2,4,5,6,7,10,11

I need a query which will return result as
From To
0 0
2 2
4 7
10 11

Mani

16 May 12 at 12:30 pm
Dear Mani,
I assume you’re only searching for missing sequences, so it would be more simpler if you just pin piont them out by using the query from the earlier posts. Unless I understood otherwise then please do explain more.
Thanks.
Mo’ Kassab

Mo' Kassab

22 May 12 at 10:29 am
i have a seq 1,2,3,4,5,6,7

my missing num is 1

how to get this??

thanks

NIbedita

30 Apr 13 at 3:06 am
i can see from your post that number ’1′ already exists so how could it be missing?
kindly advise.

thnx

Mo’ Kassab

Mo' Kassab

2 May 13 at 8:36 am

Xaprb