Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 18-02-2009
Member
 
Join Date: Jan 2009
Posts: 48
SQL Query for Searching Missing Numbers from Sequence of Numbers

Hi there.

I am trying for a query which helps in finding the first missing number within the sequence of number in a database. Consider the example that , I have a set of fields which may are having the numbers from 1-10 and number missing 11, have the numbers from 12-15, and missing 16, and similar kind.

I want to perform through the table and explain that 11 as a number is the first number missing out of the sequence of number. This is regarding for an issue of employee numbers in standard order, but in the past there were problems at random so I am trying to clear it out and issue all the missing numbers. So when each time I will run the query, it will find the next lowest missing number.

Please help me with this.
Reply With Quote
  #2  
Old 18-02-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: SQL Query for Searching Missing Numbers from Sequence of Numbers

Check this query:

Select Top 1 YourNumber + 1 from YourTable T1
where not exists
(select * from YourTable T2 where T2.YourNumber = T1.YourNumber + 1)
order by YourNumber asc
Reply With Quote
  #3  
Old 18-02-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: SQL Query for Searching Missing Numbers from Sequence of Numbers

var calc = from r in Daily
join pa in Sparse on
r.deptno equals pa.deptno
where pa.date.Equals(
(from maxpa in Sparse
where maxpa.date <= r.date
&& pa.deptno == maxpa.deptno
select maxpa.date ).()Max()
)
select new { r.date, r.deptno, r.name, r.data1, r.data2
//, v =

+
// rand.Next().ToString()
//,
// Allocation = pa
}
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #4  
Old 18-02-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: SQL Query for Searching Missing Numbers from Sequence of Numbers

Try this one:

SQL> insert into ORDERS_TABLE (…, ORDER_NUMBER, …)
values (…, (select max(ORDER_NUMBER)
from ORDERS_TABLE) + 1,…);

SQL> create trigger ORDER_TABLE_SEQ
after insert on ORDERS_TABLE(update ORDERS_TABLE set ORDER_NUMBER =
(select count(*) from ORDERS_TABLE)where DBKEY = ORDERS_TABLE.DBKEY
) for each row;

SQL> create trigger SEQUENCE_NUM_TRIG
after insert on ORDERS_TABLE
(update SEQ_TABLE set SEQ_TABLE.NUMBER = (select count(*) from ORDERS_TABLE) + 1)
for each row;

SQL> create sequence DEPT_ID;

SQL> create sequence COST_CENTER
minvalue 100
maxvalue 999;

SQL> create sequence CUSTOMER_ID_SEQ
minvalue integer
maxvalue integer;
show sequence customer_id_seq
CUSTOMER_ID_SEQ
Sequence Id: 2

SQL> create sequence COST_CENTER
start with 245
minvalue 100
maxvalue 999;

SQL> SET FLAGS 'SEQ_CACHE(10000)';

$ define/user_mode rdms$set_flags “SEQ_CACHE(10000)”
$ rmu/load/commit_every=600 mydatabase mytable bulkload.dat
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "SQL Query for Searching Missing Numbers from Sequence of Numbers"
Thread Thread Starter Forum Replies Last Post
How to change negative numbers to positive numbers in Excel Shaina Na Microsoft Project 3 08-01-2012 05:35 PM
Need C program code to count numbers of negative & positive numbers Sarfaraj Khan Software Development 5 16-01-2010 02:00 PM
Random Numbers in C Rob Dizzle Software Development 5 30-12-2009 10:37 AM
How to Use Chapter Numbers with Page Numbers In Ms Word Braze Windows Software 3 27-12-2008 07:01 PM
Random numbers. Help tetilda Software Development 2 15-09-2008 05:52 PM


All times are GMT +5.5. The time now is 12:46 PM.