Perfect. Thank You. I knew there had to be something simple.
From: Peter Steinheuser [mailto:psteinheu...@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 ------------+------------ 3 | 2 3 | 8 4 | 10 4 | 11 (4 rows) 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
