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, <strong>row_number() over (partition by subsetid order by subsetid)</strong> as subsetposition, name frøm tblTable
Simple!