add an auto increment column to a result set

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)

add an auto increment column to a result set

Author

Topic

bobyliu
Starting Member

14 Posts

Posted - 2005-01-21 : 12:06:32

For MS Sql, is there a way to add a column that functions like an auto increment field to a result set?

for example,

select rotatedomain, setid, i++ from domainrotation

the i++ part is the part i am talking about.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-21 : 13:38:14

use pubs
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by 1

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

Kristen
Test

22859 Posts

Posted - 2005-01-21 : 13:44:28

You could select to a temporary table first, and append an IDENTITY column to be incorporated in the temporary column, but all the ways are hard compared to some sort of ROW_IDENTITY command - which is not available in SQL2k and earlier

Kristen

BlackDog
Starting Member

18 Posts

Posted - 2005-01-21 : 14:10:35

I used to do this using a little trick, but now I can't remember the trick. It looked something like:

declare @i int

set @i = 0

select au_fname, seq = @i = @i + 1
from authors

But that obviously isn't it. Does that ring a bell with anyone?

Kristen
Test

22859 Posts

Posted - 2005-01-21 : 14:42:43

You can only do that with an UPDATE statement AFAIK


declare @i int
set @i = 0

UPDATE authors
SET seq = @i = @i + 1,
    au_fname = 'xxx'

Kristen

bobyliu
Starting Member

14 Posts

Posted - 2005-01-21 : 15:03:44

I seem to be getting some kind of syntax error with the '=' sign. am i missing something here?

BlackDog
Starting Member

18 Posts

Posted - 2005-01-21 : 15:08:49

If you're working with the sample I posted, then no, you're not missing anything. What I posted does not work. I was attempting to see if there was an alternative, but couldn't remember the exact details.

I believe Kristen is right in that what I posted only works with UPDATE statements. Sorry for the distraction.

bobyliu
Starting Member

14 Posts

Posted - 2005-01-21 : 15:13:32

yeah... that's what i mean. I was trying out the one with a update statement.

I am doing something like

declare @i int
set @i = 1

UPDATE @Domains SET [id] = @i = @i + 1

but i am getting a syntax error near =

BlackDog
Starting Member

18 Posts

Posted - 2005-01-21 : 15:26:16

It was a bit backwards. This should work.

DECLARE @tblDates table(cnt int null)
DECLARE @i int

SET @i = 0

WHILE(@i < 20)
BEGIN
INSERT @tblDates VALUES (NULL)

SET @i = @i + 1
END

SET @i = 0

UPDATE @tblDates
SET @i = cnt = @i + 1

SELECT cnt FROM @tblDates

bobyliu
Starting Member

14 Posts

Posted - 2005-01-21 : 16:04:12

Cool. It works great. Thanks BlackDog!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources