Revisions to Find the missing numbers in a given range

Reviewed question with new edits

Source Link

edited Mar 9, 2016 at 21:03

932
5
10

I use tables that have a dimdate or count data to do this kind of calculation, but you could also do this using the identity column of a table that has at least 60 sequential identities.

Your other option is to select 60 or so rows from another table, and use the RowNumber() function. You would then right join to that select, and find the null values in your original list.

SELECT * 
FROM StartEnd (Your original table)
RIGHT JOIN (SELECT ROWNUMBER() as num 
            FROM SomeTable (any table with 60 rows) 
            WHERE SomeTable.column1 <= 60) 
    as SomeTableSubQuery on SomeTableSubQuery.num = StartEnd.numbers
WHERE StartEnd.numbers IS NULL

Sorry if this answer is convoluted. I can be more specific if you give me column names, etc.

EDIT: I thought you meant one column, not two, my bad.

I use tables that have a dimdate or count data to do this kind of calculation, but you could also do this using the identity column of a table that has at least 60 sequential identities.

Your other option is to select 60 or so rows from another table, and use the RowNumber() function. You would then right join to that select, and find the null values in your original list.

SELECT * 
FROM StartEnd (Your original table)
RIGHT JOIN (SELECT ROWNUMBER() as num 
            FROM SomeTable (any table with 60 rows) 
            WHERE SomeTable.column1 <= 60) 
    as SomeTableSubQuery on SomeTableSubQuery.num = StartEnd.numbers
WHERE StartEnd.numbers IS NULL

Sorry if this answer is convoluted. I can be more specific if you give me column names, etc.

I use tables that have a dimdate or count data to do this kind of calculation, but you could also do this using the identity column of a table that has at least 60 sequential identities.

Your other option is to select 60 or so rows from another table, and use the RowNumber() function. You would then right join to that select, and find the null values in your original list.

SELECT * 
FROM StartEnd (Your original table)
RIGHT JOIN (SELECT ROWNUMBER() as num 
            FROM SomeTable (any table with 60 rows) 
            WHERE SomeTable.column1 <= 60) 
    as SomeTableSubQuery on SomeTableSubQuery.num = StartEnd.numbers
WHERE StartEnd.numbers IS NULL

Sorry if this answer is convoluted. I can be more specific if you give me column names, etc.

EDIT: I thought you meant one column, not two, my bad.

improved formatting

Source Link

edit approved Mar 9, 2016 at 20:51

Vladimir S.

495
5
12

I use tables that have a dimdate or count data to do this kind of calculation, but you could also do this using the identity column of a table that has at least 60 sequential identities.

Your other option is to select 60 or so rows from another table, and use the RowNumber() function. You would then right join to that select, and find the null values in your original list.

SELECT * from
FROM StartEnd (Your original table)
RIGHT JOIN (SELECT ROWNUMBER() as num from
            FROM SomeTable (any table with 60 rows) 
where            WHERE SomeTable.column1 <= 60) 
    as SomeTableSubQuery on SomeTableSubQuery.num = StartEnd.numbers
whereWHERE StartEnd.numbers isIS nullNULL

Sorry if this answer is convoluted. I can be more specific if you give me column names, etc.

I use tables that have a dimdate or count data to do this kind of calculation, but you could also do this using the identity column of a table that has at least 60 sequential identities.

Your other option is to select 60 or so rows from another table, and use the RowNumber() function. You would then right join to that select, and find the null values in your original list.

SELECT * from StartEnd (Your original table)
RIGHT JOIN (SELECT ROWNUMBER() as num from SomeTable (any table with 60 rows) 
where SomeTable.column1 <= 60) as SomeTableSubQuery on SomeTableSubQuery.num = StartEnd.numbers
where StartEnd.numbers is null

Sorry if this answer is convoluted. I can be more specific if you give me column names, etc.

I use tables that have a dimdate or count data to do this kind of calculation, but you could also do this using the identity column of a table that has at least 60 sequential identities.

Your other option is to select 60 or so rows from another table, and use the RowNumber() function. You would then right join to that select, and find the null values in your original list.

SELECT * 
FROM StartEnd (Your original table)
RIGHT JOIN (SELECT ROWNUMBER() as num 
            FROM SomeTable (any table with 60 rows) 
            WHERE SomeTable.column1 <= 60) 
    as SomeTableSubQuery on SomeTableSubQuery.num = StartEnd.numbers
WHERE StartEnd.numbers IS NULL

Sorry if this answer is convoluted. I can be more specific if you give me column names, etc.

Source Link

created Mar 9, 2016 at 20:32

Arthur D

932
5
10

I use tables that have a dimdate or count data to do this kind of calculation, but you could also do this using the identity column of a table that has at least 60 sequential identities.

Your other option is to select 60 or so rows from another table, and use the RowNumber() function. You would then right join to that select, and find the null values in your original list.

SELECT * from StartEnd (Your original table)
RIGHT JOIN (SELECT ROWNUMBER() as num from SomeTable (any table with 60 rows) 
where SomeTable.column1 <= 60) as SomeTableSubQuery on SomeTableSubQuery.num = StartEnd.numbers
where StartEnd.numbers is null

Sorry if this answer is convoluted. I can be more specific if you give me column names, etc.

Stack Exchange Network

Return to Answer