display counter - 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)

display counter

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-07-13 : 08:43:52

I have this data...


SELECT tbl.*
FROM (
	select 'item name 01' as item, 25 as price
	union all
	select 'item name 02' as item, 26 as price
	union all
	select 'item name 03' as item, 28 as price	
	) as tbl

I want to put a counter on each row. how?
Desired result is...


ctr         item         price       
----------- ------------ ----------- 
1           item name 01 25
2           item name 02 26
3           item name 03 28

(3 row(s) affected)

Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-13 : 08:49:26

with tbl(item, price)
as
( select 'item name 01' as item, 25 as price
union all
select 'item name 02' as item, 26 as price
union all
select 'item name 03' as item, 28 as price
)
select num = (select count(*) from tbl t2 where t2.item <= t.item), *
from tbl t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-07-13 : 08:56:04

error nr

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.

Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-13 : 09:03:00

You're probably using v2000.
If you are running this with a derived table then just add the count to the derived table.
If it's a proper table then just run the select statement from my code.

select num = (select count(*) from tbl t2 where t2.item <= t.item), *
from tbl t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-13 : 09:53:21

Also, if you use front end application, do numbering there if possible

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources