How to generate auto incrementing number

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

SQL Server Development (2000)

How to generate auto incrementing number

Author

Topic

holymac
Starting Member

13 Posts

Posted - 2004-09-22 : 04:28:47

Hi everyone,

I am having a table with which i generate a report. Now how to using a SQL query i can generate a auto incrementing no.

Say i am executing this query

SELECT NAME,AGE,ADDRESS FROM MEMBER which gives

NAME AGE ADDRESS
HOLYMAC 13 MALACCA
KALYANA 25 USA
RATNAM 55 LONDON

Now how can i make it come out like this

SNO NAME AGE ADDRESS
1 HOLYMAC 13 MALACCA
2 KALYANA 25 USA
3 RATNAM 55 LONDON

See the first column is a auto incrementing number 1,2,3.

I have writtent this Query but as I was testing

declare @test Int
set @test = 1
select (select @test + 1) as Sno ,Name,Age,Address from member

but the output is giving the same no for all the record like this

SNO NAME AGE ADDRESS
2 HOLYMAC 13 MALACCA
2 KALYANA 25 USA
2 RATNAM 55 LONDON

How can we write a SQL query that outputs a auto incrementing number.

Thank you
Have nice day

sabirpatel
Starting Member

22 Posts

Posted - 2004-09-22 : 04:38:28

Hi,

First of all the problem with you query is you have set @test = 1 in the first step so it will always initialize @test to 1 and hence give you the result that you have specified.

The solution to this is very simple

Go to enterprise manager
select the table and column
below you can find identity,identityseed, identity increment option to select. That will autoincrement the sr no

holymac
Starting Member

13 Posts

Posted - 2004-09-22 : 04:46:45

Sabir Patel,

I do realize the functions of Indentity and stuff.That is not the problem that i am facing.Simple put, imagine i have a table that dont have a identity column. Now when i retrive the records using SELECT stmt how to have a column say SNO that displays a auto increasing no.

holymac
Starting Member

13 Posts

Posted - 2004-09-22 : 04:50:54

Disregard this query, i was simply testing the odds.
declare @test Int
set @test = 1
select (select @test + 1) as Sno ,Name,Age,Address from member

I know another alternative would be create a table and then dump the records and then select back the records.But i want see if there are other way to it.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-22 : 05:01:01

create table variable then

insert into @table select identity(int,1,1) as RowID,fields... from tblSource

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 05:12:47

this should do it:

select rank = count(*), t1.*
from member t1, member t2
where t1.name + cast(t1.age as varchar(5)) + t1.address >= t2.name + cast(t2.age as varchar(5)) + t2.address
group by t1.name, t1.age, t1.address
order by 1

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources