counter of the group by statment

hi,

Lets say I have a table called test

create table test(

number int,

[str] nvarchar(50)

)

table content, for example:

8,'a'

8,'b'

8,'c'

5,'a'

5,'h'

I want to get a result with one more column that indicates on the asc counter

of the group by clause. i.e.

somthing like this:

number counter

8, 1

5, 2

and so on...

it does not realy matter regarding the query - All I want is to add one more column to the query that indicates on the count of the group by.so if i have 2 rows with number 8 and 2 rows with number 5 I would like to see in that column 1 (at the 8 number rows) and 2 (on the 5 number rows) and so on.. as the number is changing the counter is going up by 1.

Hi,

I think you might need to take a look at the RANK function: http://msdn2.microsoft.com/en-us/library/ms176102.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

You can use the dense_rank function to do this. The sample query assumes that you want to return all rows

create table #test(

number int,

[str] nvarchar(50)

)

insert into #test
select 8,'a'
union all
select 8,'b'
union all
select 8,'c'
union all
select 5,'a'
union all
select 5,'h'

select number,
dense_rank() over (order by number) as dense_rank
from #test

drop table #test
go

number dense_rank
-- --
5 1
5 1
8 2
8 2
8 2

|||

Hi,

Thank you for this info, but I need to use this rank in my where clause:

select number,

dense_rank() over (order by number) as dense_rank

from #test

where dense_rank = 1

but i get an error. How can I use it in the where clause?

thx