Re: 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@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:

 

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