From pozer@automotive.com Tue Jan 11 19:17:56 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id A76711337B83 for ; Tue, 11 Jan 2011 15:17:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 24136-07 for ; Tue, 11 Jan 2011 19:17:49 +0000 (UTC) X-Greylist: delayed 00:17:21.254185 by SQLgrey-1.7.6 Received: from mail1.automotive.com (mail1.automotive.com [12.131.237.150]) by mail.postgresql.org (Postfix) with ESMTP id 335901337B49 for ; Tue, 11 Jan 2011 15:17:48 -0400 (AST) Received: from mail1.automotive.com ([10.0.0.23]) by mail1.automotive.com with Microsoft SMTPSVC(6.0.3790.4675); Tue, 11 Jan 2011 11:00:26 -0800 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CBB1C1.CE69B8AC" Subject: Getting top 2 by Category Date: Tue, 11 Jan 2011 11:00:26 -0800 Message-ID: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018440@mail-001.corp.automotive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Getting top 2 by Category Thread-Index: Acuxwc53ufS+sJpJS520OYwT8R+dwg== From: "Ozer, Pam" To: X-OriginalArrivalTime: 11 Jan 2011 19:00:26.0246 (UTC) FILETIME=[CE8ECA60:01CBB1C1] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/25 X-Sequence-Number: 34972 This is a multi-part message in MIME format. ------_=_NextPart_001_01CBB1C1.CE69B8AC Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable This is probably very simple but I am drawing a blank. Do I need to create a cursor to iterate through a table to grab the top 2 magazines per category? Here is my table and some data . The results I need are at the bottom. Any help would be greatly appreciated: =20 CREATE TABLE magazinecategory ( magazinecategoryid smallint NOT NULL , magazineid smallint, categoryid smallint ); =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (1, 2, 3); =20 =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (2, 8, 3); =20 =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (3 9, 3); =20 =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (4, 10, 4); =20 =20 =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (5, 11, 4); =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (6, 12,4); =20 =20 =20 The results I want are CategoryID MagazineID 3 2 3 8 4 10 4 11 =20 =20 =20 Pam Ozer ------_=_NextPart_001_01CBB1C1.CE69B8AC Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

This is = probably very simple but I am drawing a blank. Do I need to create = a cursor to iterate through a table to grab the top 2 magazines per = category? Here is my table and some data . The results I = need are at the bottom. Any help would be greatly = appreciated:

CREATE TABLE magazinecategory

(

= magazinecategoryid smallint NOT NULL ,

magazineid smallint,

categoryid smallint

);

INSERT INTO = magazinecategory(

&= nbsp; magazinecategoryid, magazineid, categoryid)

VALUES (1, 2, 3);

INSERT INTO = magazinecategory(

&= nbsp; magazinecategoryid, magazineid, categoryid)

VALUES (2, 8, 3);

INSERT INTO = magazinecategory(

&= nbsp; magazinecategoryid, magazineid, categoryid)

VALUES (3 9, 3);

INSERT INTO = magazinecategory(

&= nbsp; magazinecategoryid, magazineid, categoryid)

VALUES (4, 10, 4);

INSERT INTO = magazinecategory(

&= nbsp; magazinecategoryid, magazineid, categoryid)

VALUES (5, 11, 4);

INSERT INTO = magazinecategory(

&= nbsp; magazinecategoryid, magazineid, categoryid)

VALUES (6, 12,4);

The results = I want are

CategoryID = MagazineID

3 2

3 8

4 = 10

4 11

P= am Ozer

------_=_NextPart_001_01CBB1C1.CE69B8AC-- From psteinheuser@myyearbook.com Tue Jan 11 20:00:18 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id AE2D01337B67 for ; Tue, 11 Jan 2011 16:00:18 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 09654-03 for ; Tue, 11 Jan 2011 20:00:11 +0000 (UTC) X-Greylist: delayed 00:08:12.49439 by SQLgrey-1.7.6 Received: from mail-fx0-f46.google.com (mail-fx0-f46.google.com [209.85.161.46]) by mail.postgresql.org (Postfix) with ESMTP id 9372A1337B5D for ; Tue, 11 Jan 2011 16:00:10 -0400 (AST) Received: by fxm20 with SMTP id 20so20292359fxm.19 for ; Tue, 11 Jan 2011 12:00:09 -0800 (PST) Received: by 10.223.81.70 with SMTP id w6mr37123fak.62.1294775516156; Tue, 11 Jan 2011 11:51:56 -0800 (PST) MIME-Version: 1.0 Received: by 10.223.115.143 with HTTP; Tue, 11 Jan 2011 11:51:36 -0800 (PST) In-Reply-To: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018440@mail-001.corp.automotive.com> References: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018440@mail-001.corp.automotive.com> From: Peter Steinheuser Date: Tue, 11 Jan 2011 14:51:36 -0500 Message-ID: Subject: Re: Getting top 2 by Category To: "Ozer, Pam" Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=20cf3054a5b3b456aa0499976aa1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201101/27 X-Sequence-Number: 34974 --20cf3054a5b3b456aa0499976aa1 Content-Type: text/plain; charset=ISO-8859-1 Well, if yoi have PG 8.4 and above - select categoryid, magazineid from ( select row_number() over (partition by categoryid order by categoryid,magazineid asc) as row_number, categoryid, magazineid from magazinecategory) foo where row_number < 3; categoryid | magazineid ------------+------------ 3 | 2 3 | 8 4 | 10 4 | 11 (4 rows) On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam wrote: > This is probably very simple but I am drawing a blank. Do I need to create > a cursor to iterate through a table to grab the top 2 magazines per > category? Here is my table and some data . The results I need are at the > bottom. Any help would be greatly appreciated: > > > > CREATE TABLE magazinecategory > > ( > > magazinecategoryid smallint NOT NULL , > > magazineid smallint, > > categoryid smallint > > ); > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (1, 2, 3); > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (2, 8, 3); > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (3 9, 3); > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (4, 10, 4); > > > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (5, 11, 4); > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (6, 12,4); > > > > > > > > The results I want are > > CategoryID MagazineID > > 3 2 > > 3 8 > > 4 10 > > 4 11 > > > > > > > > *Pam Ozer* > -- Peter Steinheuser psteinheuser@myyearbook.com --20cf3054a5b3b456aa0499976aa1 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Well, if yoi have PG 8.4 and above -

select categoryid, magazineid f= rom (
select row_number() over (partition by categoryid order by categor= yid,magazineid=A0 asc) as row_number,
=A0categoryid, magazineid from mag= azinecategory) foo
where row_number < 3;
=A0categoryid | magazineid
------------+---= ---------
=A0=A0=A0=A0=A0=A0=A0=A0=A0 3 |=A0=A0=A0=A0=A0=A0=A0=A0=A0 2=A0=A0=A0=A0=A0=A0=A0=A0=A0 3 |=A0=A0=A0=A0=A0=A0=A0=A0=A0 8
=A0=A0=A0= =A0=A0=A0=A0=A0=A0 4 |=A0=A0=A0=A0=A0=A0=A0=A0 10
=A0=A0=A0=A0=A0=A0=A0= =A0=A0 4 |=A0=A0=A0=A0=A0=A0=A0=A0 11
(4 rows)

On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam <pozer@automotive.com> wrote:

This is probably very simple but I am drawing a blank.=A0 Do I need to = create a cursor to iterate through a table to grab the top 2 magazines per = category?=A0 Here is my table and some data .=A0 The results I need are at = the=A0 bottom.=A0 Any help would be greatly appreciated:

=A0
CREATE TABLE magazinec= ategory
(
=A0 magazinec= ategoryid smallint NOT NULL ,
=A0 magazineid smal= lint,
=A0 categoryid smallint
);
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, magazi= neid, categoryid)
=A0=A0=A0 VALUES (1, 2, 3);
=A0
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, m= agazineid, categoryid)
=A0=A0=A0 VALUES (2, 8, 3);
=A0
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, m= agazineid, categoryid)
=A0=A0=A0 VALUES (3 9, 3);
=A0
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, ma= gazineid, categoryid)
=A0=A0=A0 VALUES (4, 10, 4);
=A0
=A0
=A0
INSER= T INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 magazinecategoryid, magazineid, categoryid)

=A0=A0=A0 VALUES (5, 11, 4);
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, magazi= neid, categoryid)
=A0=A0=A0 VALUES (6, 12,4);
=A0
=A0
=A0
The re= sults I want are
CategoryID=A0 MagazineID
3 2

3 8
4 10
4 11
=A0
=A0
=A0
Pam Ozer

-- Peter Steinheuser
pstei= nheuser@myyearbook.com
--20cf3054a5b3b456aa0499976aa1-- From pozer@automotive.com Tue Jan 11 19:53:17 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 774B61337B5D for ; Tue, 11 Jan 2011 15:53:17 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 03367-02 for ; Tue, 11 Jan 2011 19:53:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail1.automotive.com (mail1.automotive.com [12.131.237.150]) by mail.postgresql.org (Postfix) with ESMTP id 8C6E31337B5C for ; Tue, 11 Jan 2011 15:53:09 -0400 (AST) Received: from mail1.automotive.com ([10.0.0.23]) by mail1.automotive.com with Microsoft SMTPSVC(6.0.3790.4675); Tue, 11 Jan 2011 11:53:08 -0800 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CBB1C9.2B799D62" Subject: Re: Getting top 2 by Category Date: Tue, 11 Jan 2011 11:53:08 -0800 Message-ID: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018442@mail-001.corp.automotive.com> In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Getting top 2 by Category Thread-Index: AcuxyQLeMuZv6NWdTEeGHA2CjoJ/lgAABz+Q References: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018440@mail-001.corp.automotive.com> From: "Ozer, Pam" To: "Peter Steinheuser" Cc: X-OriginalArrivalTime: 11 Jan 2011 19:53:08.0532 (UTC) FILETIME=[2B6D5740:01CBB1C9] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/26 X-Sequence-Number: 34973 This is a multi-part message in MIME format. ------_=_NextPart_001_01CBB1C9.2B799D62 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Perfect. Thank You. I knew there had to be something simple. =20 From: Peter Steinheuser [mailto:psteinheuser@myyearbook.com]=20 Sent: Tuesday, January 11, 2011 11:52 AM To: Ozer, Pam Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Getting top 2 by Category =20 Well, if yoi have PG 8.4 and above - select categoryid, magazineid from ( select row_number() over (partition by categoryid order by categoryid,magazineid asc) as row_number, categoryid, magazineid from magazinecategory) foo=20 where row_number < 3; categoryid | magazineid=20 ------------+------------ 3 | 2 3 | 8 4 | 10 4 | 11 (4 rows) On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam wrote: This is probably very simple but I am drawing a blank. Do I need to create a cursor to iterate through a table to grab the top 2 magazines per category? Here is my table and some data . The results I need are at the bottom. Any help would be greatly appreciated: =20 CREATE TABLE magazinecategory ( magazinecategoryid smallint NOT NULL , magazineid smallint, categoryid smallint ); =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (1, 2, 3); =20 =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (2, 8, 3); =20 =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (3 9, 3); =20 =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (4, 10, 4); =20 =20 =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (5, 11, 4); =20 INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (6, 12,4); =20 =20 =20 The results I want are CategoryID MagazineID 3 2 3 8 4 10 4 11 =20 =20 =20 Pam Ozer --=20 Peter Steinheuser psteinheuser@myyearbook.com ------_=_NextPart_001_01CBB1C9.2B799D62 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Perfect. Thank You. I knew there had to be something = simple.

From:= = Peter Steinheuser [mailto:psteinheuser@myyearbook.com]
Sent: = Tuesday, January 11, 2011 11:52 AM
To: Ozer, Pam
Cc: = pgsql-sql@postgresql.org
Subject: Re: [SQL] Getting top 2 by = Category

Well, if yoi have PG 8.4 and above = -

select categoryid, magazineid from (
select row_number() = over (partition by categoryid order by categoryid,magazineid asc) = as row_number,
categoryid, magazineid from magazinecategory) = foo
where row_number < 3;
categoryid | magazineid =
------------+------------
      &nbs= p;   3 |          = 2
          3 = |          = 8
          4 = |         = 10
          4 = |         11
(4 = rows)

On Tue, Jan 11, = 2011 at 2:00 PM, Ozer, Pam <pozer@automotive.com> = wrote:

This is = probably very simple but I am drawing a blank. Do I need to create = a cursor to iterate through a table to grab the top 2 magazines per = category? Here is my table and some data . The results I = need are at the bottom. Any help would be greatly = appreciated:

<= /o:p>

CREATE = TABLE magazinecategory

(=

= magazinecategoryid smallint NOT NULL ,

= magazineid smallint,

= categoryid smallint

);

<= /o:p>

INSERT INTO = magazinecategory(

= = magazinecategoryid, magazineid, categoryid)

= VALUES (1, 2, 3);

<= /o:p>

<= /o:p>

INSERT INTO = magazinecategory(

= = magazinecategoryid, magazineid, categoryid)

= VALUES (2, 8, 3);

<= /o:p>

<= /o:p>

INSERT INTO = magazinecategory(

= = magazinecategoryid, magazineid, categoryid)

= VALUES (3 9, 3);

<= /o:p>

<= /o:p>

INSERT INTO = magazinecategory(

= = magazinecategoryid, magazineid, categoryid)

= VALUES (4, 10, 4);

<= /o:p>

<= /o:p>

<= /o:p>

INSERT INTO = magazinecategory(

= = magazinecategoryid, magazineid, categoryid)

= VALUES (5, 11, 4);

<= /o:p>

INSERT INTO = magazinecategory(

= = magazinecategoryid, magazineid, categoryid)

= VALUES (6, 12,4);

<= /o:p>

<= /o:p>

<= /o:p>

The results = I want are

CategoryID&n= bsp; MagazineID

3 = 2

3 = 8

4 = 10

4 = 11

<= /o:p>

Pam Ozer

--
Peter Steinheuser
psteinheuser@myyearbook.com

------_=_NextPart_001_01CBB1C9.2B799D62-- From msi77@yandex.ru Tue Jan 11 20:08:17 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 474F71337B5D for ; Tue, 11 Jan 2011 16:08:17 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 98386-10 for ; Tue, 11 Jan 2011 20:08:09 +0000 (UTC) X-Greylist: delayed 00:07:05.628401 by SQLgrey-1.7.6 Received: from forward4.mail.yandex.net (forward4.mail.yandex.net [77.88.46.9]) by mail.postgresql.org (Postfix) with ESMTP id A34A31337B64 for ; Tue, 11 Jan 2011 16:08:08 -0400 (AST) Received: from web61.yandex.ru (web61.yandex.ru [77.88.46.52]) by forward4.mail.yandex.net (Yandex) with ESMTP id A09D26AD8EE7; Tue, 11 Jan 2011 23:00:57 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex.ru; s=mail; t=1294776057; bh=3HChygDGS0XAVYOFUP9dmtyyWn2ZWkYh+kKKxTpKgvo=; h=From:To:In-Reply-To:References:Subject:MIME-Version:Message-Id: Date:Content-Transfer-Encoding:Content-Type; b=PQdwnjEHZ8I2Hj2XP1PEOq2/xwumT9IUahTAfAFJcHc1XEED7jc4Ku/ls1RFaCQLa Zq9lxpdivOwdRBoPy7LQ52l5WLBZ3FZ/zfkjVVXvZmlHFi3lovlYBtUelbPWNOdpFe N2oWOXvel4nQEgs7nQUIBTsatVBUizyoYnujZruU= Received: from localhost (localhost.localdomain [127.0.0.1]) by web61.yandex.ru (Yandex) with ESMTP id 90280BC8075; Tue, 11 Jan 2011 23:00:57 +0300 (MSK) Received: from z10-13.opera-mini.net (z10-13.opera-mini.net [82.145.208.144]) by mail.yandex.ru with HTTP; Tue, 11 Jan 2011 23:00:55 +0300 From: msi77 To: "Ozer, Pam" ,pgsql-sql@postgresql.org In-Reply-To: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018440@mail-001.corp.automotive.com> References: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018440@mail-001.corp.automotive.com> Subject: Re: Getting top 2 by Category MIME-Version: 1.0 Message-Id: <143611294776056@web61.yandex.ru> Date: Tue, 11 Jan 2011 23:00:55 +0300 X-Mailer: Yamail [ http://yandex.ru ] 5.0 Content-Transfer-Encoding: base64 Content-Type: text/plain; charset=koi8-r X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.925 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, FSL_RU_URL=2.271, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: * X-Archive-Number: 201101/28 X-Sequence-Number: 34975 VGhlcmUgc29tZSB3YXlzIHRvIGRvIHRoaXMgaW4gb25lIHF1ZXJ5LiBMb29rIGhlcmU6DQpodHRw Oi8vd3d3LnNxbC1leC5ydS9oZWxwL3NlbGVjdDE2LnBocA0KDQotLS0tLS0tLS0tLS0tLS0NCjEx LjAxLjExLCAyMjowMCwgIk96ZXIsIFBhbSIgPHBvemVyQGF1dG9tb3RpdmUuY29tPjo+IFRoaXMg aXMgcHJvYmFibHkgdmVyeSBzaW1wbGUgYnV0IEkgYW0gZHJhd2luZyBhIGJsYW5rLpogRG8gSSBu ZWVkIHRvIGNyZWF0ZSBhIGN1cnNvciB0byBpdGVyYXRlIHRocm91Z2ggYSB0YWJsZSB0byBncmFi IHRoZSB0b3AgMiBtYWdhemluZXMgcGVyIGNhdGVnb3J5P5ogSGVyZSBpcyBteSB0YWJsZSBhbmQg c29tZSBkYXRhIC6aIFRoZSByZXN1bHRzIEkgbmVlZCBhcmUgYXQgdGhlmiBib3R0b20umiBBbnkg aGVscCB3b3VsZCBiZSBncmVhdGx5IGFwcHJlY2lhdGVkOppDUkVBVEUgVEFCTEUgbWFnYXppbmVj YXRlZ29yeSiaIG1hZ2F6aW5lY2F0ZWdvcnlpZCBzbWFsbGludCBOT1QgTlVMTCAsmiBtYWdhemlu ZWlkIHNtYWxsaW50LJogY2F0ZWdvcnlpZCBzbWFsbGludCk7mklOU0VSVCBJTlRPIG1hZ2F6aW5l Y2F0ZWdvcnkompqampqampqampogbWFnYXppbmVjYXRlZ29yeWlkLCBtYWdhemluZWlkLCBjYXRl Z29yeWlkKZqamiBWQUxVRVMgKDEsIDIsIDMpO5qaSU5TRVJUIElOVE8gbWFnYXppbmVjYXRlZ29y eSiampqampqampqamiBtYWdhemluZWNhdGVnb3J5aWQsIG1hZ2F6aW5laWQsIGNhdGVnb3J5aWQp mpqaIFZBTFVFUyAoMiwgOCwgMyk7mppJTlNFUlQgSU5UTyBtYWdhemluZWNhdGVnb3J5KJqampqa mpqampqaIG1hZ2F6aW5lY2F0ZWdvcnlpZCwgbWFnYXppbmVpZCwgY2F0ZWdvcnlpZCmampogVkFM VUVTICgzIDksIDMpO5qaSU5TRVJUIElOVE8gbWFnYXppbmVjYXRlZ29yeSiampqampqampqamiBt YWdhemluZWNhdGVnb3J5aWQsIG1hZ2F6aW5laWQsIGNhdGVnb3J5aWQpmpqaIFZBTFVFUyAoNCwg MTAsIDQpO5qamklOU0VSVCBJTlRPIG1hZ2F6aW5lY2F0ZWdvcnkompqampqampqampogbWFnYXpp bmVjYXRlZ29yeWlkLCBtYWdhemluZWlkLCBjYXRlZ29yeWlkKZqamiBWQUxVRVMgKDUsIDExLCA0 KTuaSU5TRVJUIElOVE8gbWFnYXppbmVjYXRlZ29yeSiampqampqampqamiBtYWdhemluZWNhdGVn b3J5aWQsIG1hZ2F6aW5laWQsIGNhdGVnb3J5aWQpmpqaIFZBTFVFUyAoNiwgMTIsNCk7mpqaVGhl IHJlc3VsdHMgSSB3YW50IGFyZUNhdGVnb3J5SUSaIE1hZ2F6aW5lSUQzIDIzIDg0IDEwNCAxMZqa mlBhbSBPemVyPg== From cgourofino@hotmail.com Wed Jan 19 20:11:15 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 176EB1337BD7 for ; Wed, 19 Jan 2011 16:11:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 85109-09 for ; Wed, 19 Jan 2011 20:11:07 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f46.google.com (mail-pz0-f46.google.com [209.85.210.46]) by mail.postgresql.org (Postfix) with ESMTP id A84041337B90 for ; Wed, 19 Jan 2011 16:11:01 -0400 (AST) Received: by pzk35 with SMTP id 35so228202pzk.19 for ; Wed, 19 Jan 2011 12:11:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:sender:reply-to:in-reply-to :references:date:x-google-sender-auth:message-id:subject:from:to :content-type; bh=Zt3xRrxb8GIOaeJhC1d945xtNqmeVxO2DjibB56s5qw=; b=mg3095k0c1me6l0Xq8VtQitTHtfKLb5DpbMfpdUTEHDq9tSspANUmnVJrgHlPlWHg4 ddlfkQrtrDwMekQq7qvNhlYOln7Qr24+f6DjukVuS0O0657QDTfUgum4QpbqeUNQSlGj P9DKkmmmbsE/wjlTS4nMEY5/FMBuBHrTm/w7Q= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:reply-to:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:content-type; b=w5Vw1nw2UicHcvHelr0NjYP1apdCJ1bY3AIYoj3jEOgdmvC99lxzWxg2itIUX9MK1m m4msSxtjR7W1Rp9ZAVI5spN1EbAyWj888zhwSp5B6dD0PJAttqw59M3fwre7VeTGgy4t 60gt8KNG+DM70/ketnHw51gHTQuMzOYt12Gck= MIME-Version: 1.0 Received: by 10.142.230.3 with SMTP id c3mr1157102wfh.290.1295467861065; Wed, 19 Jan 2011 12:11:01 -0800 (PST) Reply-To: cgourofino@hotmail.com Received: by 10.142.68.12 with HTTP; Wed, 19 Jan 2011 12:11:01 -0800 (PST) In-Reply-To: References: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018440@mail-001.corp.automotive.com> Date: Wed, 19 Jan 2011 18:11:01 -0200 X-Google-Sender-Auth: jsBjOa6F1O6yBhY8CYnRPdAgcVk Message-ID: Subject: Re: Getting top 2 by Category From: Carla To: Peter Steinheuser , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd32c8cad40d7049a389d89 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.724 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FORGED_HOTMAIL_RCVD2=0.874, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201101/54 X-Sequence-Number: 35001 --000e0cd32c8cad40d7049a389d89 Content-Type: text/plain; charset=ISO-8859-1 2011/1/11 Peter Steinheuser > Well, if yoi have PG 8.4 and above - > > select categoryid, magazineid from ( > select row_number() over (partition by categoryid order by > categoryid,magazineid asc) as row_number, > categoryid, magazineid from magazinecategory) foo > where row_number < 3; > categoryid | magazineid > ------------+------------ > > 3 | 2 > 3 | 8 > 4 | 10 > 4 | 11 > (4 rows) > > How can I do it in PG 8.3? > > > On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam wrote: > >> This is probably very simple but I am drawing a blank. Do I need to >> create a cursor to iterate through a table to grab the top 2 magazines per >> category? Here is my table and some data . The results I need are at the >> bottom. Any help would be greatly appreciated: >> >> >> >> CREATE TABLE magazinecategory >> >> ( >> >> magazinecategoryid smallint NOT NULL , >> >> magazineid smallint, >> >> categoryid smallint >> >> ); >> >> >> >> INSERT INTO magazinecategory( >> >> magazinecategoryid, magazineid, categoryid) >> >> VALUES (1, 2, 3); >> >> >> >> >> >> INSERT INTO magazinecategory( >> >> magazinecategoryid, magazineid, categoryid) >> >> VALUES (2, 8, 3); >> >> >> >> >> >> INSERT INTO magazinecategory( >> >> magazinecategoryid, magazineid, categoryid) >> >> VALUES (3 9, 3); >> >> >> >> >> >> INSERT INTO magazinecategory( >> >> magazinecategoryid, magazineid, categoryid) >> >> VALUES (4, 10, 4); >> >> >> >> >> >> >> >> INSERT INTO magazinecategory( >> >> magazinecategoryid, magazineid, categoryid) >> >> VALUES (5, 11, 4); >> >> >> >> INSERT INTO magazinecategory( >> >> magazinecategoryid, magazineid, categoryid) >> >> VALUES (6, 12,4); >> >> >> >> >> >> >> >> The results I want are >> >> CategoryID MagazineID >> >> 3 2 >> >> 3 8 >> >> 4 10 >> >> 4 11 >> >> >> >> >> >> >> >> *Pam Ozer* >> > > > > -- > Peter Steinheuser > psteinheuser@myyearbook.com > --000e0cd32c8cad40d7049a389d89 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

2011/1/11 Peter Steinheuser &l= t;psteinheuser@myyearbook.co= m>

Well, if yoi have PG 8.4 and above -

select categoryid, magazineid f= rom (
select row_number() over (partition by categoryid order by categor= yid,magazineid=A0 asc) as row_number,
=A0categoryid, magazineid from mag= azinecategory) foo
where row_number < 3;
=A0categoryid | magazineid
------------+---= ---------

=A0=A0=A0=A0=A0=A0=A0=A0=A0 3 |=A0=A0=A0=A0= =A0=A0=A0=A0=A0 2
=A0=A0=A0=A0=A0=A0=A0=A0=A0 3 |=A0=A0=A0=A0=A0=A0=A0= =A0=A0 8
=A0=A0=A0=A0=A0=A0=A0=A0=A0 4 |=A0=A0=A0=A0=A0=A0=A0=A0 10
= =A0=A0=A0=A0=A0=A0=A0=A0=A0 4 |=A0=A0=A0=A0=A0=A0=A0=A0 11
(4 rows)

How ca= n I do it in PG 8.3?
=A0

On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam <pozer@automotive.com>= ; wrote:
This is probably very simple but I am drawing a blank.=A0 Do I need to = create a cursor to iterate through a table to grab the top 2 magazines per = category?=A0 Here is my table and some data .=A0 The results I need are at = the=A0 bottom.=A0 Any help would be greatly appreciated:

=A0
CREATE TABLE magazinec= ategory
(
=A0 magazinec= ategoryid smallint NOT NULL ,
=A0 magazineid smal= lint,
=A0 categoryid smallint
);
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, magazi= neid, categoryid)
=A0=A0=A0 VALUES (1, 2, 3);
=A0
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, m= agazineid, categoryid)
=A0=A0=A0 VALUES (2, 8, 3);
=A0
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, m= agazineid, categoryid)
=A0=A0=A0 VALUES (3 9, 3);
=A0
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, ma= gazineid, categoryid)
=A0=A0=A0 VALUES (4, 10, 4);
=A0
=A0
=A0
INSER= T INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 magazinecategoryid, magazineid, categoryid)

=A0=A0=A0 VALUES (5, 11, 4);
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, magazi= neid, categoryid)
=A0=A0=A0 VALUES (6, 12,4);
=A0
=A0
=A0
The re= sults I want are
CategoryID=A0 MagazineID
3 2

3 8
4 10
4 11
=A0
=A0
=A0
Pam Ozer

--
Peter Steinheuser
psteinheuser@myyearbook.co= m

--000e0cd32c8cad40d7049a389d89-- From psteinheuser@myyearbook.com Wed Jan 19 21:00:06 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C6C6F1337B54 for ; Wed, 19 Jan 2011 17:00:06 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 49601-03 for ; Wed, 19 Jan 2011 20:59:59 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f46.google.com (mail-fx0-f46.google.com [209.85.161.46]) by mail.postgresql.org (Postfix) with ESMTP id D2C721337B5C for ; Wed, 19 Jan 2011 16:59:57 -0400 (AST) Received: by fxm20 with SMTP id 20so1324011fxm.19 for ; Wed, 19 Jan 2011 12:59:56 -0800 (PST) Received: by 10.223.74.1 with SMTP id s1mr1154365faj.138.1295470796749; Wed, 19 Jan 2011 12:59:56 -0800 (PST) MIME-Version: 1.0 Received: by 10.223.115.143 with HTTP; Wed, 19 Jan 2011 12:59:35 -0800 (PST) In-Reply-To: References: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018440@mail-001.corp.automotive.com> From: Peter Steinheuser Date: Wed, 19 Jan 2011 15:59:35 -0500 Message-ID: Subject: Re: Getting top 2 by Category To: cgourofino@hotmail.com Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=20cf30433dfea83d46049a394cbe X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201101/55 X-Sequence-Number: 35002 --20cf30433dfea83d46049a394cbe Content-Type: text/plain; charset=ISO-8859-1 There's probably several ways - not saying this is best/optimal. SELECT categoryid, magazineid FROM magazinecategory a WHERE ( SELECT COUNT(*) FROM magazinecategory WHERE categoryid = a.categoryid AND magazineid <= a.magazineid ) < 3 order by categoryid, magazineid; On Wed, Jan 19, 2011 at 3:11 PM, Carla wrote: > 2011/1/11 Peter Steinheuser > >> Well, if yoi have PG 8.4 and above - >> >> select categoryid, magazineid from ( >> select row_number() over (partition by categoryid order by >> categoryid,magazineid asc) as row_number, >> categoryid, magazineid from magazinecategory) foo >> where row_number < 3; >> categoryid | magazineid >> ------------+------------ >> >> 3 | 2 >> 3 | 8 >> 4 | 10 >> 4 | 11 >> (4 rows) >> >> > How can I do it in PG 8.3? > > >> >> >> On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam wrote: >> >>> This is probably very simple but I am drawing a blank. Do I need to >>> create a cursor to iterate through a table to grab the top 2 magazines per >>> category? Here is my table and some data . The results I need are at the >>> bottom. Any help would be greatly appreciated: >>> >>> >>> >>> CREATE TABLE magazinecategory >>> >>> ( >>> >>> magazinecategoryid smallint NOT NULL , >>> >>> magazineid smallint, >>> >>> categoryid smallint >>> >>> ); >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (1, 2, 3); >>> >>> >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (2, 8, 3); >>> >>> >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (3 9, 3); >>> >>> >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (4, 10, 4); >>> >>> >>> >>> >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (5, 11, 4); >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (6, 12,4); >>> >>> >>> >>> >>> >>> >>> >>> The results I want are >>> >>> CategoryID MagazineID >>> >>> 3 2 >>> >>> 3 8 >>> >>> 4 10 >>> >>> 4 11 >>> >>> >>> >>> >>> >>> >>> >>> *Pam Ozer* >>> >> >> >> >> -- >> Peter Steinheuser >> psteinheuser@myyearbook.com >> > > -- Peter Steinheuser psteinheuser@myyearbook.com --20cf30433dfea83d46049a394cbe Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable There's probably several ways - not saying this is best/optimal.
SELECT
=A0 categoryid, magazineid
FROM
=A0 magazinecategory a
= WHERE (
=A0 SELECT
=A0=A0=A0 COUNT(*)
=A0 FROM
=A0=A0=A0 magazi= necategory
=A0 WHERE
=A0=A0=A0 categoryid =3D a.categoryid
=A0 AND
=A0=A0=A0 magazineid &l= t;=3D a.magazineid
) < 3
order by categoryid, magazineid;

<= br>

On Wed, Jan 19, 2011 at 3:11 PM, Carla <cgourofino@ho= tmail.com> wrote:

2011/1/11 Peter Steinheuser <psteinheuser@myyearbook.c= om>

Well, if yoi have PG 8.4 and above -

select categoryid, magazineid f= rom (
select row_number() over (partition by categoryid order by categor= yid,magazineid=A0 asc) as row_number,
=A0categoryid, magazineid from mag= azinecategory) foo
where row_number < 3;
=A0categoryid | magazineid
------------+---= ---------

=A0=A0=A0=A0=A0=A0=A0=A0=A0 3 |=A0=A0=A0=A0=A0=A0=A0=A0= =A0 2
=A0=A0=A0=A0=A0=A0=A0=A0=A0 3 |=A0=A0=A0=A0=A0=A0=A0=A0=A0 8
= =A0=A0=A0=A0=A0=A0=A0=A0=A0 4 |=A0=A0=A0=A0=A0=A0=A0=A0 10
=A0=A0=A0=A0= =A0=A0=A0=A0=A0 4 |=A0=A0=A0=A0=A0=A0=A0=A0 11
(4 rows)

How can I do it in = PG 8.3?
=A0

On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam <pozer@automotive.com>= ; wrote:
This is probably very simple but I am drawing a blank.=A0 Do I need to = create a cursor to iterate through a table to grab the top 2 magazines per = category?=A0 Here is my table and some data .=A0 The results I need are at = the=A0 bottom.=A0 Any help would be greatly appreciated:

=A0
CREATE TABLE magazinec= ategory
(
=A0 magazinec= ategoryid smallint NOT NULL ,
=A0 magazineid smal= lint,
=A0 categoryid smallint
);
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, magazi= neid, categoryid)
=A0=A0=A0 VALUES (1, 2, 3);
=A0
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, m= agazineid, categoryid)
=A0=A0=A0 VALUES (2, 8, 3);
=A0
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, m= agazineid, categoryid)
=A0=A0=A0 VALUES (3 9, 3);
=A0
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, ma= gazineid, categoryid)
=A0=A0=A0 VALUES (4, 10, 4);
=A0
=A0
=A0
INSER= T INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 magazinecategoryid, magazineid, categoryid)

=A0=A0=A0 VALUES (5, 11, 4);
=A0
INSERT INTO magazinecategory(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 magazinecategoryid, magazi= neid, categoryid)
=A0=A0=A0 VALUES (6, 12,4);
=A0
=A0
=A0
The re= sults I want are
CategoryID=A0 MagazineID
3 2

3 8
4 10
4 11
=A0
=A0
=A0
Pam Ozer

--
Peter Steinheuser
psteinheuser@m= yyearbook.com

--
Peter Steinheuser
psteinheuser@myyearbook.com
--20cf30433dfea83d46049a394cbe--