-2

I have table with identity column seqno. I want to find missing no in this column.

Note : I have only read permission in this table only.

My code:

SELECT Rno 
FROM 
    (SELECT 
         seqno, ROW_NUMBER() OVER (ORDER BY seqno) AS Rno 
     FROM trnmast) a 
WHERE 
    seqno <> rno

I run this but not getting correct result.

Example :

SeqNo
1
3
4
7
8
10

I want only missing no like this :

seqNo
2
5
6
9

I have only read permission of this table only.

Piyush Kachhadiya
  • 617
  • 8
  • 18
  • Dare I ask: what's the motivation / need to find those "missing" sequence numbers? What if you've found all those "missing" sequence numbers - then what? – marc_s Oct 06 '22 at 12:42
  • I want to find list of missing number in my auto increment column. because of I have another table that is dependent on this column so, I need to find missing number and getting right data from back up table. – Piyush Kachhadiya Oct 06 '22 at 12:49
  • I can't access of back table direct so, I need missing no. – Piyush Kachhadiya Oct 06 '22 at 12:51
  • 1
    `select top((select max(seqno) from trnmast)) row_number() over(order by @@spid) as seqno from sys.all_columns as a cross join sys.all_columns as b except select seqno from trnmast` – lptr Oct 06 '22 at 13:00
  • Does this answer your question? [SQL: find missing IDs in a table](https://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table) – Meyssam Toluie Oct 06 '22 at 13:13
  • @lptr thanks your replay , your query is right it's give perfect answered. But problem is there I have no access other future of database. only read permission in this table can you help without using `sys.all_columns`. – Piyush Kachhadiya Oct 07 '22 at 04:01
  • Does getting a list of numbers in your other table that do not exist in the trnmast table solve your issue? – Joe Oct 08 '22 at 13:50
  • @MeyssamToluie is it not. I have do this with permission. – Piyush Kachhadiya Oct 12 '22 at 07:05
  • @Joe I have write down in my question I have limited access of database. – Piyush Kachhadiya Oct 12 '22 at 07:06

4 Answers4

2

Just an option where you DON'T need to create a tally table. It uses an ad-hoc tally table.

Example

Select N 
 From ( Select Top (Select max(SeqNo) From TRNMAST) 
               N= Row_Number() Over (Order By (Select NULL)) 
         From  TRNMAST n1, TRNMAST n2  ) N
 Left Join TRNMAST
  on  SeqNo=N 
 Where SeqNo is Null

Results

N
2
5
6
9

Results

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
1

You can create a lookup table (a table variable) contains the seqno from 1 to the maximum number in your table.

Query

declare @t as table(seqno int);
declare @n as int, @s as int;
set @s = 1;
select @n = max(seqno) from yourTable;
while(@s <= @n)
begin
  insert into @t
  select @s;
  set @s += 1;
end

select * from @t
where seqno not in (select seqno from yourTable);
Ullas
  • 11,450
  • 4
  • 33
  • 50
  • thanks for replay, I was run this with change of `int` to `bigint` datatype, it's tack to much time. can you suggest for more faster way. – Piyush Kachhadiya Oct 07 '22 at 07:00
0

The easiest would be to have a "tally" (numbers) table (that contains a column Number with the numbers, from 1 through (n) - however high you want to go) and then do something like this:

SELECT
    number
FROM
    dbo.TallyTable tt
LEFT OUTER JOIN
    dbo.trnmast trn ON tt.Number = trn.seqno
WHERE
    trn.seqno IS NULL

Take the tally table, left join to your trnmast table on the seqno and get those entries from the tally table that aren't present in the trnmast table.

Of course - you must first have (or create) such a tally table - there are lots of blog posts and articles on how to do that - just do some research and you'll find ways to do this easily!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • @marc-s thanks for replay, but I can't do this because of I have no permission in database to create table, I have only read permission of this table so it's not work for me. – Piyush Kachhadiya Oct 07 '22 at 04:10
0
DECLARE @minimum INT
SELECT @minimum = MIN(seqno) FROM #temp

;WITH Missing (missnum, maxid)
AS
(
 SELECT @minimum AS missnum, (select max(seqno) from #temp)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN #temp tt on tt.seqno = Missing.missnum
WHERE tt.seqno is NULL
OPTION (MAXRECURSION 0);

Result : enter link description here

Piyush Kachhadiya
  • 617
  • 8
  • 18