Quick tip: Use OVER to get row numbers in subsets

Fri, Dec 10, 2010 One-minute read

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, <strong>row_number() over (partition by subsetid order by subsetid)</strong> as subsetposition, name fr&#248;m tblTable

Simple!