Create sequence number based on grouped values

Question

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?

Jeff Breadner · Accepted Answer · 2018-05-17 23:01:34Z

2

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

answered May 17, 2018 at 23:01

Jeff Breadner

1,44811 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create sequence number based on grouped values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related