Partition your SQL SELECT results into groups

Luke Canvin

The second of a series of posts introducing handy functions built into SQL Server (2005 and above) to help you include interesting metadata about your SELECT statement's results.

  1. Include row numbers in your SQL SELECT results
  2. Partition your SQL SELECT results into groups

Partition By

If you'd like to partition your SELECT statement's result set into groups, which can be treated separately for things like row-counts or aggregate functions, then the Partition By syntax is perfect. Let's look at an example, say you run the following query:

Select SortName,
       TestScore,
       Row_Number() Over (Order By Forename) As Row_Number
From   T_Tests
Where  Surname = 'Lesseps'

Which uses the Row_Number() function we looked at in the previous article. This gives us the following result set:

SortName             TestScore Row_Number
-----------------    --------- ----------
Lesseps, Aaliyah     98        1
Lesseps, Aaliyah     95        2
Lesseps, Aaliyah     85        3
Lesseps, Aaliyah     80        4
Lesseps, Aaliyah     68        5
Lesseps, Aaralyn     73        6
Lesseps, Aaralyn     89        7
Lesseps, Aaron       87        8
Lesseps, Abby        100       9
Lesseps, Abby        85        10
Lesseps, Abby        90        11
Lesseps, Abby        84        12
Lesseps, Abigail     98        13
Lesseps, Abigail     83        14
Lesseps, Abrianna    70        15
Lesseps, Abrianna    88        16
Lesseps, Adam        75        17
Lesseps, Addison     88        18
Lesseps, Addison     87        19
Lesseps, Addison     68        20
Lesseps, Addison     76        21

We can simply drop in the Partition By syntax in combination with our Row_Number function to give us, for example, row numbers grouped by Forename:

Select SortName,
       TestScore,
       Row_Number() Over (Order By Forename) As Row_Number,
       Row_Number() Over (Partition By Forename Order By Forename) As P_Row_Number
From   T_Tests
Where  Surname = 'Lesseps'
SortName             TestScore Row_Number   P_Row_Number
------------------   --------- -----------  ------------
Lesseps, Aaliyah     82        1            1
Lesseps, Aaliyah     62        2            2
Lesseps, Aaliyah     77        3            3
Lesseps, Aaliyah     76        4            4
Lesseps, Aaliyah     84        5            5
Lesseps, Aaralyn     61        6            1
Lesseps, Aaralyn     71        7            2
Lesseps, Aaron       76        8            1
Lesseps, Abby        64        9            1
Lesseps, Abby        76        10           2
Lesseps, Abby        78        11           3
Lesseps, Abby        64        12           4
Lesseps, Abigail     92        13           1
Lesseps, Abigail     75        14           2
Lesseps, Abrianna    74        15           1
Lesseps, Abrianna    65        16           2
Lesseps, Adam        65        17           1
Lesseps, Addison     94        18           1
Lesseps, Addison     97        19           2
Lesseps, Addison     64        20           3
Lesseps, Addison     72        21           4

Partitioning with aggregate functions

A great time-saving use of the Partition By syntax is to use it with aggregate functions. Extending our example above, we could do the following:

Select SortName,
       TestScore,
       Row_Number() Over (Order By Forename) As Row_Number,
       Row_Number() Over (Partition By Forename Order By Forename) As P_Row_Number,
       Count(TestScore) Over (Partition By Forename Order By Forename) As Count,
       Avg(TestScore) Over (Partition By Forename Order By Forename) As Avg,
       Min(TestScore) Over (Partition By Forename Order By Forename) As Min,
       Max(TestScore) Over (Partition By Forename Order By Forename) As Max
From   T_Tests
Where  Surname = 'Lesseps'

Which would give us:

SortName             TestScore Row_Number   P_Row_Number   Count  Avg    Min   Max
------------------   --------- ----------   ------------   -----  -----  ----  ---
Lesseps, Aaliyah     67        1            1              5      79.20  65    97
Lesseps, Aaliyah     90        2            2              5      79.20  65    97
Lesseps, Aaliyah     97        3            3              5      79.20  65    97
Lesseps, Aaliyah     65        4            4              5      79.20  65    97
Lesseps, Aaliyah     77        5            5              5      79.20  65    97
Lesseps, Aaralyn     76        6            1              2      87.50  76    99
Lesseps, Aaralyn     99        7            2              2      87.50  76    99
Lesseps, Aaron       98        8            1              1      98.00  98    98
Lesseps, Abby        79        9            1              4      83.50  71    93
Lesseps, Abby        91        10           2              4      83.50  71    93
Lesseps, Abby        71        11           3              4      83.50  71    93
Lesseps, Abby        93        12           4              4      83.50  71    93
Lesseps, Abigail     66        13           1              2      72.00  66    78
Lesseps, Abigail     78        14           2              2      72.00  66    78
Lesseps, Abrianna    81        15           1              2      80.00  79    81
Lesseps, Abrianna    79        16           2              2      80.00  79    81
Lesseps, Adam        91        17           1              1      91.00  91    91
Lesseps, Addison     91        18           1              4      76.25  60    91
Lesseps, Addison     60        19           2              4      76.25  60    91
Lesseps, Addison     91        20           3              4      76.25  60    91
Lesseps, Addison     63        21           4              4      76.25  60    91

Much easier than writing sub-queries or incorporating awkward group-by clauses to achieve the same thing.

Leave a Reply