2011/1/11 Peter Steinheuser <psteinheu...@myyearbook.com> > 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 <po...@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 > psteinheu...@myyearbook.com >
