CODEWITHSUNDEEP
sql-servert-sql
Nayan
Nayan

Reputation: 3214

Need group by counter as column returned

I have a table:

declare @tbl as table (
  Name nvarchar(10), Property int
)

Sample data in the table is like:

Name | Property
'A'  | 0
'A'  | 1
'A'  | 2
'A'  | 3
'B'  | 0
'B'  | 2
'B'  | 1
'C'  | 0

How can I return this table along with a column, that depicts the grouped column index?

Expected result:

Name | Property | Index
'A'  | 0        | 1
'A'  | 1        | 1
'A'  | 2        | 1
'A'  | 3        | 1
'B'  | 0        | 2
'B'  | 2        | 2
'B'  | 1        | 2
'C'  | 0        | 3

Please suggest! Thanks!

Update:

I used dense_rank() (in wrong fashion, I think) like this

SELECT 
   *, 
   DENSE_RANK() OVER (PARTITION BY [Name] ORDER BY [Property]) AS R
FROM @tbl

Upvotes: 3

Views: 71

Answers (2)

Amit Singh
Amit Singh

Reputation: 8109

try like this...

Select Name,Property ,Dense_Rank() OVER 
    (ORDER BY Name) AS [Index] from TableName

Upvotes: 1

Ian Preston
Ian Preston

Reputation: 39566

Use DENSE_RANK:

select *
  , [Index] = dense_rank() over (order by Name)
from tbl

SQL Fiddle with demo.

Upvotes: 4

Related Questions