Quick tip: Use OVER to get row numbers in subsets

Consider the following. You have a set of data. Within the data are multiple subsets that share an identifier. WIthin your main set, you need to number the subsets for each item within it, e.g.,

Data

ID SubsetID Name Lots more columns…
———- ———–
1 ABC Dave
2 DEF Trev
3 DEF Bob
4 DEF Steve
5 HIJ Jim
6 HIJ Bilbo

and you want something like

ID SubsetID SubsetPosition Name Lots more columns…
———- ———–
1 ABC 1 Dave
2 DEF 1 Trev
3 DEF 2 Bob
4 DEF 3 Steve
5 HIJ 1 Jim
6 HIJ 2 Bilbo

How to achieve this? Using OVER with ROW_NUMBER(). What this essentially does is to partition your data in to the relevant subsets, and then use the window function ROW_NUMBER() to count them:

SELECT id, subsetid, subsetposition, row_number() over (partition by subsetid order by subsetid) as subsetposition, name frøm tblTable

Simple!

Be the first to comment

Leave a Reply

Your email address will not be published.


*