From: | Carla <cgourofino(at)hotmail(dot)com> |
---|---|
To: | Peter Steinheuser <psteinheuser(at)myyearbook(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Getting top 2 by Category |
Date: | 2011-01-19 20:11:01 |
Message-ID: | AANLkTimpL6FJ=AcaXGNSZiF8FUuUmxHC4QHGY1zNEeWy@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
2011/1/11 Peter Steinheuser <psteinheuser(at)myyearbook(dot)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 <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 | Peter Steinheuser | 2011-01-19 20:59:35 | Re: Getting top 2 by Category |
Previous Message | Kenneth Marshall | 2011-01-19 14:18:38 | Re: question about reg. expression |