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

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 <pozer (AT) automotive (DOT) com> wrote:



--
Peter Steinheuser
psteinheuser (AT) myyearbook (DOT) com

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



From: Peter Steinheuser [mailtosteinheuser (AT) myyearbook (DOT) com]
Sent: Tuesday, January 11, 2011 11:52 AM
To: Ozer, Pam
Cc: pgsql-sql (AT) postgresql (DOT) 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
------------+------------
3 | 2
3 | 8
4 | 10
4 | 11
(4 rows)



On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam <pozer (AT) automotive (DOT) 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:



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 (AT) myyearbook (DOT) com

2011/1/11 Peter Steinheuser <psteinheuser (AT) myyearbook (DOT) com>

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 <cgourofino (AT) hotmail (DOT) com> wrote:


--
Peter Steinheuser
psteinheuser (AT) myyearbook (DOT) com