CODEWITHSUNDEEP
t-sql
Westerlund.io
Westerlund.io

Reputation: 2833

Create sequence number based on grouped values

I would like to create a sequence number based on some of the columns. In the example below I would like to group on Column1 and Column2 without losing the data in Column3.

Column1     Column2         Column3
A           SharedData      Data1
A           SharedData      Data2
A           SharedData      Data3
B           SharedData      Data1
B           SharedData      Data2
B           SharedData      Data3

Expected results

Seq     Column1     Column2         Column3
1       A           SharedData      Data1
2       A           SharedData      Data2
3       A           SharedData      Data3
1       B           SharedData      Data1
2       B           SharedData      Data2
3       B           SharedData      Data3

I read around and I tried out ROW_NUMBER(), DENSE_RANK() and RANK() but I'm not sure if those work or how one would implement those in my case.

Is it possible to get the expected result?

Upvotes: 0

Views: 43

Answers (1)

Jeff Breadner
Jeff Breadner

Reputation: 1438

row_number() is the correct window function to use.

'partition by' makes it so that you get a different sequence of numbers for each distinct value in the partition by specification (there can be more than one column specified here), and the sequence is sorted by the 'order by' specification (again, it can be more than one column; each column can be sorted asc or desc, same as the normal 'order by' syntax).

This window function was introduced in SQL Server 2008.

select
  row_number() over (partition by Column1 order by Column3) as Seq,
  Column1,
  Column2,
  Column3
from
  mytable

Upvotes: 2

Related Questions