Database Reference
In-Depth Information
25003900300 | 1211.000 | 1438.2307692307692308
:
ORDER BY
Window functions also allow an
ORDER BY
in the
OVER
clause. Without getting too ab‐
struse, the best way to think about this is that all the rows in the window will be ordered
as indicated by
ORDER BY
, and the window function will consider only rows that range
from the first row in the window up to and including the current row in the window or
partition. The classic example uses the
ROW_NUMBER
function to sequentially number
rows. In
Example 7-25
, we demonstrate how to number our census tracts in alphabetical
order. To arrive at the row number,
ROW_NUMBER
counts all rows up to and including
current row based on the order dictated by the
ORDER BY
.
Example 7-25. Numbering using ROW_NUMBER window function
SELECT
ROW_NUMBER
()
OVER
(
ORDER
BY
tract_name
)
As
rnum
,
tract_name
FROM
census
.
lu_tracts
ORDER
BY
rnum
LIMIT
4
;
rnum | tract_name
-----+--------------------------------------------------
1 | Census Tract 1, Suffolk County, Massachusetts
2 | Census Tract 1001, Suffolk County, Massachusetts
3 | Census Tract 1002, Suffolk County, Massachusetts
4 | Census Tract 1003, Suffolk County, Massachusetts
In
Example 7-25
, we also have an
ORDER BY
for the entire query. Don't get confused
between this and the
ORDER BY
that's specific to the window function.
You can combine
ORDER BY
with
PARTITION BY
, restarting the ordering for each parti‐
tion.
Example 7-26
returns to our example of county codes.
Example 7-26. Combining PARTITION BY and ORDER BY
SELECT
tract_id
,
val
,
SUM
(
val
)
OVER
(
PARTITION
BY
left
(
tract_id
,
5
)
ORDER
BY
val
)
As
sum_county_ordered
FROM
census
.
facts
WHERE
fact_type_id
=
2
ORDER
BY
left
(
tract_id
,
5
),
val
;
tract_id | val | sum_county_ordered
-------------+----------+--------------------
25001014100 | 226.000 | 226.000
25001011700 | 971.000 | 1197.000
25001010208 | 984.000 | 2181.000
:
25003933200 | 564.000 | 564.000
25003934200 | 593.000 | 1157.000
25003931300 | 606.000 | 1763.000
:
