March 23, 2010 at 1:28 pm
I have a table with a column which is not a primary key but which I increment by 1 each time a new record is added.
So, the rows will end up with 1 in the first row, 2 in the second etc.
The thing is any row might subsequently be deleted. So I could end up with say 5 rows and then one of them gets deleted. So I might end up with 1,2,4,5 as the values in that column.
If that happens, next time I add a new record, I want to put the missing one back - in this case the '3'.
How can I find the first row whose value in that column is not 1 more than the row before? And grab the value of the column in the row before so I can add 1 to it when I insert the new row.
I know I can do this with a cursor - is there any way of doing it without a cursor?
Thanks for any help.
March 23, 2010 at 1:35 pm
Here's one way:
select min(YourColumn)
from dbo.YourTable as T1
where not exists
(select 1
from dbo.YourTable as T2
where T2.ID = T1.ID + 1);
You'll have to plug in your actual table and column names, of course. Does that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2010 at 1:54 pm
Here's another way (note that it uses a virtual tally table - if you already have one, jump to the final select statement). If there are no gaps, this also gets the next number. You need to ensure that the tally table has enough rows being returned to cover all the rows in your table.
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)
SELECT TOP (1) N
FROM Tally
LEFT JOIN <YourTable> yt
ON yt.<YourColumn> = Tally.N
WHERE yt.YourColumn IS NULL
ORDER BY N
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 23, 2010 at 2:35 pm
GSquared - thanks very much for that - it does exactly what I need.
Wayne, thanks for your reply. I keep reading about the benefits of a tally table but have never needed one before. So I've created one now and can (finally) see what they're for.
Thanks again.
March 23, 2010 at 2:37 pm
Can you mark posts as resolved on this forum?
March 24, 2010 at 6:46 am
sku370870 (3/23/2010)
Can you mark posts as resolved on this forum?
No, because you never know when someone else might find the question or discussion interesting and make another contribution.
Hey Wayne, nice in-line tally table 😀 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 24, 2010 at 9:00 am
Paul White NZ (3/24/2010)
sku370870 (3/23/2010)
Can you mark posts as resolved on this forum?No, because you never know when someone else might find the question or discussion interesting and make another contribution.
Hey Wayne, nice in-line tally table 😀 😉
Thanks... just goes to show that I can still learn better ways. Thanks for pointing out the UNION ALL;-)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply