Received: from localhost (pluto.hub.org [200.46.204.4]) by postgresql.org (Postfix) with ESMTP id C26FC9FA28E for ; Mon, 20 Nov 2006 13:09:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024) with ESMTP id 66152-09 for ; Mon, 20 Nov 2006 13:08:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 0C3C79FA285 for ; Mon, 20 Nov 2006 13:08:59 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from web.hteamericas.com (web.hteamericas.com [65.164.54.20]) by svr4.postgresql.org (Postfix) with ESMTP id 5CF995AF02C for ; Mon, 20 Nov 2006 17:08:59 +0000 (GMT) Received: from miswen (pc111.hteamericas.com [192.168.10.111] (may be forged)) by web.hteamericas.com (8.13.6/8.13.4) with SMTP id kAKH8vWT001005 for ; Mon, 20 Nov 2006 12:08:57 -0500 Message-ID: <00e001c70cc6$926292a0$6f0aa8c0@miswen> From: "wen tseng" To: Subject: How to find out top 3 records in each location Date: Mon, 20 Nov 2006 12:09:00 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00DD_01C70C9C.A9724BE0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/214 X-Sequence-Number: 26866 This is a multi-part message in MIME format. ------=_NextPart_000_00DD_01C70C9C.A9724BE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On PostgreSQL, I have a table like this: Item Location Sales A X 10 B X 6 C Y 3 D Y 8 E Y 15 F Y 11 I'd like to find out top 3 items in each location and put those 3 items = as colum values like this: Location Top1 Top2 Top3 X A B Y E F D Since PostgreSQL doesn't support TOP, how can I do to get this result? Any solution will be appreciated. Thank you in advance. ------=_NextPart_000_00DD_01C70C9C.A9724BE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

On PostgreSQL, I=20 have a table like this:

Item =20 Location Sales

A =20 =20 X = =20 10

B &nb= sp; =20 X = =20 6

C &nb= sp; =20 Y = =20 3

D &nb= sp; =20 Y = =20 8

E &nb= sp; =20 Y = =20 15

F &nb= sp; Y &nb= sp; 11

I'd like to find out top 3 = items in each=20 location and put those 3 items as colum values like this:

Location = Top1 Top2 Top3

=20 X = =20 A B

=20 Y = =20 E =20 F D

Since PostgreSQL doesn't = support=20 TOP, how can I do to get this result?

Any solution will be=20 appreciated.

Thank you in=20 advance.

------=_NextPart_000_00DD_01C70C9C.A9724BE0--