From: | Peter Steinheuser <psteinheuser(at)myyearbook(dot)com> |
---|---|
To: | "Ozer, Pam" <pozer(at)automotive(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Getting top 2 by Category |
Date: | 2011-01-11 19:51:36 |
Message-ID: | AANLkTino1zYS3rxBktKhJGG2dhV4XN_SXUZS1eXRWDia@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ozer, Pam | 2011-01-11 19:53:08 | Re: Getting top 2 by Category |
Previous Message | Ozer, Pam | 2011-01-11 19:00:26 | Getting top 2 by Category |