Well, first of, you need to post what you've been able to do. That said..are you entering these values manually? I should think there'll be a predefined query that generates them.
If it's already an hard coded table and you just need to find the missing sequence, then you can also write a code for that but first, you need to show your attempt.
Create a table (or with a cartesian it can be done in a query) with the series of numbers from your smaller one to the greater one and left or right join it to your table. You'll get the missing ones when you add for criteria where your column is null.
thanks for your replies...
i am having a table name KHEWAT..
it has a feild KHEWAT_NO whose data type is VARCHAR.
this table has value from 1501 to 1986 in which there are some missing no..which i have to find out...
for that purpose i am looking for an sql query...
I don't see any effort, but I'll give you the answer as I'm too bored and there are not many threads open for my taste.
select seq.id from KHEWAT right join
(select 1501 + convert(int,(a0.id + a1.id + a2.id)) as id
(select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90) a1,
(select 0 id union select 100 union select 200 union select 300 union select 400) a2
on KHEWAT_NO = seq.id
where seq.id is null
and seq.id between 1501 and 1986
SELECT (t1.log_id + 1) AS gap_starts_at,
(SELECT MIN(t3.log_id) -1 FROM logtable t3 WHERE t3.log_id > t1.log_id) AS gap_ends_at
FROM logtable t1
WHERE NOT EXISTS (SELECT t2.log_id FROM logtable t2 WHERE t2.log_id = t1.log_id + 1)
HAVING gap_ends_at IS NOT NULL