Revision history [back]
 | 1 | initial version |
The outcome column of ROWNUMBER()OVER(ORDER BY FOO) can be used as a grouping column in an aggregate function. For example, if we need to calculate the average value of a certain column grouped by the rank (ROWNUMBER) of another column, we could do:
SELECT ROWNUMBER() OVER(ORDER BY column1) as rank, AVG(column2) as avgvalue FROM table1 GROUP BY ROW_NUMBER() OVER(ORDER BY column1)
This query will calculate the average value of column2 for each rank (ROW_NUMBER) of column1.