need a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

need a query

Author

Topic

Madhav
Starting Member

38 Posts

Posted - 2007-07-31 : 02:13:54

hi,
this may look simple please help me,

a table(10 rows) contains identity column values like this
no
1
2
3
5
6
8
9
i was deleted 4 and 7 and 10
we have to get the numbers 4 ,7 and 10 which r missed in the identity column

give me a query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-31 : 02:27:18

Refer this
http://mindsdoor.net/SQLTsql/FindGapsInSequence.html

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 02:59:05

How can you tell 10 is missing? Is not 10 and 11 missing? Or 0?

-- Prepare sample data
DECLARE	@Sample TABLE (no TINYINT)

INSERT	@Sample
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	5 UNION ALL
SELECT	6 UNION ALL
SELECT	8 UNION ALL
SELECT	9

-- Show the data
SELECT		v.Number
FROM		master..spt_values AS v
LEFT JOIN	@Sample AS s ON s.no = v.Number
WHERE		v.Type = 'p'
		AND s.no IS NULL
		AND v.Number BETWEEN (SELECT MIN(no) FROM @Sample) AND (SELECT MAX(no) FROM @Sample)

E 12°55'05.25"
N 56°04'39.16"

Madhav
Starting Member

38 Posts

Posted - 2007-07-31 : 04:32:28

i am saying 4, 7 and 10 are missing because initially i quoted
a table containg 10 rows (id col values 1 to 10)

your query works fine. But i didnot get the last record.
i.e if i delete the last 5 records, i didnt get the id col values for these records.
it is working fine for those records which were deleted in between min and max id colvalues.

for example :
i had idcol vaues as
1,2,3,4,5,6,7,8,9,10,11,12,13.

i deleted 4, 7, 12, 13.
from ur query i did get on;y 4 and 7 but not 12, 13.
is it possible to these two(12, 13) also

please help me.

quote:

Originally posted by Peso

How can you tell 10 is missing? Is not 10 and 11 missing? Or 0?

-- Prepare sample data
DECLARE	@Sample TABLE (no TINYINT)

INSERT	@Sample
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	5 UNION ALL
SELECT	6 UNION ALL
SELECT	8 UNION ALL
SELECT	9

-- Show the data
SELECT		v.Number
FROM		master..spt_values AS v
LEFT JOIN	@Sample AS s ON s.no = v.Number
WHERE		v.Type = 'p'
		AND s.no IS NULL
		AND v.Number BETWEEN (SELECT MIN(no) FROM @Sample) AND (SELECT MAX(no) FROM @Sample)

E 12°55'05.25"
N 56°04'39.16"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-31 : 06:08:43

Y

--------------------------------------------------
S.Ahamed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 06:13:31

Here is the way to do it hard-coded. If you KNOW the limits are {1, 10}, use this

-- Prepare sample data
DECLARE	@Sample TABLE (no TINYINT)

INSERT	@Sample
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	5 UNION ALL
SELECT	6 UNION ALL
SELECT	8 UNION ALL
SELECT	9

-- Show the data
SELECT		v.Number
FROM		master..spt_values AS v
LEFT JOIN	@Sample AS s ON s.no = v.Number
WHERE		v.Type = 'p'
		AND s.no IS NULL
		AND v.Number BETWEEN 1 AND 10

If you KNOW the limits are {1, 13} use this

-- Prepare sample data
DECLARE	@Sample TABLE (no TINYINT)

INSERT	@Sample
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	5 UNION ALL
SELECT	6 UNION ALL
SELECT	8 UNION ALL
SELECT	9

-- Show the data
SELECT		v.Number
FROM		master..spt_values AS v
LEFT JOIN	@Sample AS s ON s.no = v.Number
WHERE		v.Type = 'p'
		AND s.no IS NULL
		AND v.Number BETWEEN 1 AND 13

E 12°55'05.25"
N 56°04'39.16"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-31 : 06:18:32

IF the coulmn is identity(i hope it is an identity column from your post) then you can modify the Peter's query as

SELECT v.Number
FROM master..spt_values AS v
LEFT JOIN @Sample AS s ON s.no = v.Number
WHERE v.Type = 'p'
AND s.no IS NULL
AND v.Number BETWEEN (SELECT MIN(no) FROM @Sample) AND Scope_identity()

--------------------------------------------------
S.Ahamed

Madhav
Starting Member

38 Posts

Posted - 2007-07-31 : 06:47:40

hi Ahmed,
thanks for ur great help,
Ur query works fine when ur in the same session.
But when i was off the seesion and come again , the
Scope_identity() returns null as it's scope is session.

Any way i get the answer. Instead of using Scope_identity()
i can use IDENT_CURRENT('tablename'). I got the eact answer what i intended for.

thnks for giving me a way.
regards.
madhav.

quote:
Originally posted by pbguy

IF the coulmn is identity(i hope it is an identity column from your post) then you can modify the Peter's query as

SELECT v.Number
FROM master..spt_values AS v
LEFT JOIN @Sample AS s ON s.no = v.Number
WHERE v.Type = 'p'
AND s.no IS NULL
AND v.Number BETWEEN (SELECT MIN(no) FROM @Sample) AND Scope_identity()

--------------------------------------------------
S.Ahamed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 07:23:35

Not if you reset the identity value...

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources