On Feb 16, 2012, at 20:01, vpapavas <vicky.papa...@gmail.com> wrote: > 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 > >
Put the window function on the order table, perform the where-limit, then join customer to the result. Also, you are numbering rows but not imposing any kind of order before doing so. Row_number doesn't make sense with a frame clause...frame is more useful for stuff like calculating rolling sums/averages and the like - where you evaluate fields in the surrounding frame as part of the aggregate. Window functions do not affect the number of rows returned. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
