Обсуждение: [GENERAL] Ordering of window functions with no order specified?
Hi. If you use a window function and don't specify an order, will the rows be processed in the same order as the query results?
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
In this particular case, I'm wondering about row_number(), and whether I explicitly need to repeat the ordering that's already specified in the query?
SELECT a,b,c,row_number()
OVER (PARTITION BY a) -- Do I need an ORDER BY c,b here?
FROM foo
ORDER BY c,b
Also, I'm interested in both what if any behavior is guaranteed, and what gets done in practice. (i.e., a SELECT with no order doesn't have guarantees, but in practice seems to return the results in the order they were added to the table. Is it something similar here?)
Thanks!
Ken
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
Am 15.06.2017 um 06:34 schrieb Ken Tanzer: > Hi. If you use a window function and don't specify an order, will the > rows be processed in the same order as the query results? > > In this particular case, I'm wondering about row_number(), and whether > I explicitly need to repeat the ordering that's already specified in > the query? > > SELECT a,b,c,row_number() > OVER (PARTITION BY a) -- Do I need an ORDER BY c,b here? > FROM foo > ORDER BY c,b > > Also, I'm interested in both what if any behavior is guaranteed, and > what gets done in practice. (i.e., a SELECT with no order doesn't > have guarantees, but in practice seems to return the results in the > order they were added to the table. Is it something similar here?) > That's different queries and results: test=# create table foo as select s%5 a, random() as b, random() as c from generate_series(1, 20) s; SELECT 20 test=*# select a, b, c, row_number() over (partition by a) from foo order by c,b; a | b | c | row_number ---+--------------------+---------------------+------------ 3 | 0.293813084252179 | 0.00748801836743951 | 4 2 | 0.366433540824801 | 0.0825160844251513 | 3 2 | 0.646373085677624 | 0.144253523554653 | 2 1 | 0.436142998747528 | 0.149559560697526 | 4 4 | 0.766950330231339 | 0.279563031159341 | 2 4 | 0.780563669744879 | 0.36753943329677 | 4 4 | 0.521357014775276 | 0.37830171873793 | 1 3 | 0.641054477542639 | 0.438840930350125 | 3 0 | 0.23528463486582 | 0.506252389866859 | 1 1 | 0.883372921962291 | 0.607358017936349 | 2 2 | 0.0624627070501447 | 0.610814236104488 | 4 1 | 0.203920441213995 | 0.680096843745559 | 3 0 | 0.945639119483531 | 0.686336697079241 | 3 2 | 0.360363553743809 | 0.702507333364338 | 1 0 | 0.493005351629108 | 0.739280233159661 | 4 0 | 0.844849191140383 | 0.756641649641097 | 2 1 | 0.375874035060406 | 0.771526555530727 | 1 4 | 0.0844886344857514 | 0.837361172772944 | 3 3 | 0.50597962597385 | 0.841444775927812 | 2 3 | 0.0100470245815814 | 0.899044481106102 | 1 (20 Zeilen) test=*# select a, b, c, row_number() over (partition by a order by c,b) from foo order by c,b; a | b | c | row_number ---+--------------------+---------------------+------------ 3 | 0.293813084252179 | 0.00748801836743951 | 1 2 | 0.366433540824801 | 0.0825160844251513 | 1 2 | 0.646373085677624 | 0.144253523554653 | 2 1 | 0.436142998747528 | 0.149559560697526 | 1 4 | 0.766950330231339 | 0.279563031159341 | 1 4 | 0.780563669744879 | 0.36753943329677 | 2 4 | 0.521357014775276 | 0.37830171873793 | 3 3 | 0.641054477542639 | 0.438840930350125 | 2 0 | 0.23528463486582 | 0.506252389866859 | 1 1 | 0.883372921962291 | 0.607358017936349 | 2 2 | 0.0624627070501447 | 0.610814236104488 | 3 1 | 0.203920441213995 | 0.680096843745559 | 3 0 | 0.945639119483531 | 0.686336697079241 | 2 2 | 0.360363553743809 | 0.702507333364338 | 4 0 | 0.493005351629108 | 0.739280233159661 | 3 0 | 0.844849191140383 | 0.756641649641097 | 4 1 | 0.375874035060406 | 0.771526555530727 | 4 4 | 0.0844886344857514 | 0.837361172772944 | 4 3 | 0.50597962597385 | 0.841444775927812 | 3 3 | 0.0100470245815814 | 0.899044481106102 | 4 (20 Zeilen) And also different execution plans: test=*# explain analyse select a, b, c, row_number() over (partition by a) from foo order by c,b; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Sort (cost=239.18..243.43 rows=1700 width=20) (actual time=0.134..0.138 rows=20 loops=1) Sort Key: c, b Sort Method: quicksort Memory: 26kB -> WindowAgg (cost=118.22..147.97 rows=1700 width=20) (actual time=0.056..0.101 rows=20 loops=1) -> Sort (cost=118.22..122.47 rows=1700 width=20) (actual time=0.048..0.054 rows=20 loops=1) Sort Key: a Sort Method: quicksort Memory: 26kB -> Seq Scan on foo (cost=0.00..27.00 rows=1700 width=20) (actual time=0.021..0.028 rows=20 loops=1) Planning time: 0.104 ms Execution time: 0.200 ms (10 Zeilen) test=*# explain analyse select a, b, c, row_number() over (partition by a order by c,b) from foo order by c,b; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Sort (cost=247.68..251.93 rows=1700 width=20) (actual time=0.115..0.119 rows=20 loops=1) Sort Key: c, b Sort Method: quicksort Memory: 26kB -> WindowAgg (cost=118.22..156.47 rows=1700 width=20) (actual time=0.056..0.090 rows=20 loops=1) -> Sort (cost=118.22..122.47 rows=1700 width=20) (actual time=0.048..0.054 rows=20 loops=1) Sort Key: a, c, b Sort Method: quicksort Memory: 26kB -> Seq Scan on foo (cost=0.00..27.00 rows=1700 width=20) (actual time=0.019..0.025 rows=20 loops=1) Planning time: 0.100 ms Execution time: 0.173 ms (10 Zeilen) As you can see, different sort keys for the WindowAgg-Sort. Please don't mix the ORDER BY for the window-function and for the result-set. Use alwyas an explicit ORDER BY if you expect an ordered result. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
On Wednesday, June 14, 2017, Ken Tanzer <ken.tanzer@gmail.com> wrote:
SELECT a,b,c,row_number()OVER (PARTITION BY a) -- Do I need an ORDER BY c,b here?
Yes.
FROM fooORDER BY c,b
Also, I'm interested in both what if any behavior is guaranteed, and what gets done in practice. (i.e., a SELECT with no order doesn't have guarantees, but in practice seems to return the results in the order they were added to the table. Is it something similar here?)
Row numbers would be assigned in the order they are sent up by the "from foo" clause.
In practice what gets done depends on the execution plan that is chosen and nothing is guaranteed unless you specify it in the query so that the execution plan can enforce it.
David J.
Thanks for the replies!
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.