numbering in result set

Question

Post reply

numbering in result set

sonaliak

Ten Centuries

Points: 1376
More actions
November 29, 2001 at 10:02 am

#77564

work_id Work_item_code
--------------------------------
3 Sup
14 100001
5 100002
16 100003
8 IWSM
9 ES3000
10 AppDev
11 100004
I want to have a counter on this select like
work_id Work_item_code counter
----------------------------------------
3 Sup 1
14 100001 2
5 100002 3
16 100003 4
8 IWSM 5
9 ES3000 6
10 AppDev 7
11 100004 8
I can use rownum in Oracle, what do I do to get this in SQL server ?
Can I get this in one select statement ?
Thanks
Sonali

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

You can, but you need a subqeury to do it and it is inefficient (uses aggregates).

create table MyCount( MyKEy int, MyVal char( 3))

go

insert MyCount select 4, 'ABC'

insert MyCount select 5, 'DEF'

insert MyCount select 7, 'GHI'

insert MyCount select 12, 'JKL'

insert MyCount select 9, 'MNO'

insert MyCount select 6, 'PQR'

go

select

m.MyKey

, m.MyVal

, (select count(*) 'seq'

from MyCount b

where b.MyKey <= m.MyKey

) 'seq'

from MyCount m

order by m.MyKey

Steve Jones

steve@dkranch.net

sonaliak Ten Centuries Points: 1376 More actions · Answer 2

sonaliak

Ten Centuries

Points: 1376

November 29, 2001 at 10:26 am

#420508

Wow, tons of thanks....

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

You are welcome. Credit actually goes to Mr Itzak Ben-Gan and Tom Moreau. I learned this trick from their "Advanced T-SQL" book.

Steve Jones

steve@dkranch.net

baruchl SSC Veteran Points: 234 More actions · Answer 4

Hi,

I need quite the same ability to count the rows, but unfortunately, since my SQL is dynamically generated, can't rely on the fact that I will have a nice MyKey (int) column so I can sort by and make the < condition in the inner query.

Is there any option to get general row counter, regardless the query I run ?

e.g.

result

-----

1

2

3

4

5

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 5

Not that I know of. You could insert everything into a temp table with a counter and go from there.

Steve Jones

steve@dkranch.net

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 6

quote:
Hi,
I need quite the same ability to count the rows, but unfortunately, since my SQL is dynamically generated, can't rely on the fact that I will have a nice MyKey (int) column so I can sort by and make the < condition in the inner query.
Is there any option to get general row counter, regardless the query I run ?
e.g.
result
-----
1
2
3
4
5

I don't know of a generic way for any query but on a case-by-case basis, you can create a table valued user-defined function with an identity column as one of its output columns. set it to start at 1 and increment by one.

It would really be a nice addition to SQL Server.