CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Neil P
Neil P

Reputation: 3210

SQL Server - window function to generate a parittion id?

I want a query that will generate an id number for each grouping

if I have a table like 

+------+------+
| Col1 | Col2 |
+------+------+
|    1 | a    |
|    1 | a    |
|    1 | a    |
|    2 | b    |
|    2 | b    |
|    3 | c    |
+------+------+

how can I generate the number inside col1 - so that each value of 'a' will always have the same id generated.

I would imagine it looks something like:

select What_is_this_Called() over (partition by col2 order by col2) as col1 
    ,col2
from myTable

Upvotes: 1

Views: 649

Answers (1)

ventik
ventik

Reputation: 887

DENSE_RANK()

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Sample:

SELECT DENSE_RANK() OVER(ORDER BY col2) AS col1, col2 
FROM myTable

Upvotes: 3

Related Questions