Counter Column - 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)

Counter Column

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-08-16 : 07:26:34

is it possible to add a counter column in a select statement like

select pub_id, pub_name from pubs

and if there are 5 rows then we shall get output as
counter pub_id pub_name
1 P1 A
2 P2 B
3 P3 C
4 P99 D
5 P105 E

and counter is a self created column, not table's column

cDc
Starting Member

30 Posts

Posted - 2004-08-16 : 07:36:13

Hi there

the only way I know of to achieve this is to create a temporary table (or better a table variable) with an identity column - as such:

DECLARE @tempt table (counter int IDENTITY, pub_id int,pub_name varchar(50))
INSERT INTO @tempt(pub_id,pub_name) SELECT pub_id,pub_name FROM pubs
SELECT counter,pub_id,pub_name FROM @tempt

hope this helps

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-16 : 07:44:15

SELECT IDENTITY(int,1,1) AS colID, col1, col2
into #temp
FROM MyTable

select * from #temp

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-16 : 08:04:45

this is from this url: http://support.microsoft.com/default.aspx?scid=kb;en-us;186133&Product=sql

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

or check out:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37723

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

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-08-16 : 08:10:57

Thanks

spirit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources