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.
- Include row numbers in your SQL SELECT results
- 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.