Hello all, I am trying to use this query in a toy database with customers and orders in order to understand the capabilities of partitioning. In plain english what I want to do is to select the orders of each customer and return only 3 of those orders.
The query I am using is this: select c_custkey, o_orderkey, o_orderpriority, id from ( select c_custkey, o_orderkey, o_orderpriority, o_totalprice, row_number() over(PARTITION BY c_custkey ROWS between UNBOUNDED PRECEDING and 3 FOLLOWING) as id from customers left outer join orders on c_custkey = o_custkey) as temp Although I am using the frame clause ROWS between UNBOUNDED PRECEDING and 3 FOLLOWING which in my understanding should return the first row of the partition and the three following, this query returns all rows in the partition. Am I doing something wrong? Or have I understood wrong the semantics of the frame clause? I am using Postgresql v9.1 I rewrote the query like this in order to make it work: select c_custkey, o_orderkey, o_orderpriority, id from ( select c_custkey, o_orderkey, o_orderpriority, row_number() over(PARTITION BY c_custkey) as id from customers left outer join orders on c_custkey = o_custkey ) as temp where id <= 3 but the problem is that I would like to not have to compute the entire join since I am interested in only 3 orders for each customer. Thank you, Vicky -- View this message in context: http://postgresql.1045698.n5.nabble.com/Window-function-frame-clause-tp5491171p5491171.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
