DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=FlupldKSh9LZSXKm8GP7GjCd8FFm9CweSyzFgwzIHX5MRRZi2Osx/p3OyvUGFx6iA5fMV0891EI6lAPgqOXPwAQmeoOOUvPnDO47CkoyTUDY/ihtBoAYfln3VyKMfa9lhnNCqrZkKtsAeR8nxB2OJyt3nYvSoUyKKPY3UtMFPHM=
	;
References: <1329440503038-5491171.post@n5.nabble.com>
In-Reply-To: <1329440503038-5491171.post@n5.nabble.com>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <4DA2930E-CB3F-4925-8DD2-7D541E5087F0@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: Window function frame clause
Date: Thu, 16 Feb 2012 20:33:16 -0500
To: vpapavas <vicky.papavas@gmail.com>

On Feb 16, 2012, at 20:01, vpapavas <vicky.papavas@gmail.com> wrote:

> Hello all,=20
>=20
> I am trying to use this query in a toy database with customers and orders i=
n
> order to understand the capabilities of partitioning. In plain english wha=
t
> I want to do is to select the orders of each customer and return only 3 of=

> those orders.=20
>=20
> The query I am using is this:=20
> select c_custkey, o_orderkey, o_orderpriority, id from (
> select c_custkey, o_orderkey, o_orderpriority, o_totalprice, row_number()=20=

> over(PARTITION BY c_custkey ROWS between UNBOUNDED PRECEDING and 3
> FOLLOWING) as id=20
> from customers left outer join orders on c_custkey =3D o_custkey) as temp
>=20
> 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
>=20
> 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=20
> from customers left outer join orders on c_custkey =3D o_custkey ) as temp=

> where id <=3D 3
>=20
> but the problem is that I would like to not have to compute the entire joi=
n
> since I am interested in only 3 orders for each customer.=20
>=20
> Thank you,
> Vicky
>=20
>=20

Put the window function on the order table, perform the where-limit, then jo=
in 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 evalu=
ate fields in the surrounding frame as part of the aggregate.

Window functions do not affect the number of rows returned.

David J.