Date: Thu, 16 Feb 2012 17:01:43 -0800 (PST)
From: vpapavas <vicky.papavas@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1329440503038-5491171.post@n5.nabble.com>
Subject: Window function frame clause
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.