Select ID from Table (Next Available) 2

[alexanderthegreat]

I want to create a select statement that will retrieve the id from a table the greatest number


select id from homepage

so if there are 7 records it will dispay simply the number 7 or better yet the number 8

 

[SQLDenis]

select max(id) from table
or
select max(id) +1 from table -- this will display the next number

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

You're actually asking several different questions.

1. The greatest id
[tt]
Select Max(Id)
From TableName[/tt]

2. The number of records
[tt]
Select Count(*)
From TableName[/tt]

3. The next id for inserting...
[tt]
Select IsNull(Max(Id), 0) + 1 As NextId
From TableName[/tt]

(The is null part 'makes it work' when there are no records in the table.)

4. Use an identity field.

[green]-- Set up a temp table for demonstration purposes[/green]
Create Table #Temp(Id Integer [blue]Identity(1,1)[/blue], Name VarChar(10))

Insert Into #Temp(Name) Values('George')
Select [blue]Scope_Identity()[/blue] As TheNewIdNumber

Insert Into #Temp(Name) Values('Denis')
Select [blue]Scope_Identity()[/blue] As TheNewIdNumber

Drop table #Temp

So, here are several answers to your question.

Option #1 is similar to Option #3 (see below)

Option #2 is horrible because the number of records will NOT correspond to the id numbers as soon as you delete your first record.

Option #3 is pretty good because it returns the next ID number incremented by 1 and accomodates a table without any records. There is a problem with this method when working in a muli-user environment because 2 users could be adding the same ID at the same time.

Option #4 is the preferred method because it protects you in a multi-user environment.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom