Re: Getting top 2 by Category

There's probably several ways - not saying this is best/optimal.

SELECT
categoryid, magazineid
FROM
magazinecategory a
WHERE (
SELECT
COUNT(*)
FROM
magazinecategory
WHERE
categoryid = a.categoryid
AND
magazineid <= a.magazineid
) < 3
order by categoryid, magazineid;

On Wed, Jan 19, 2011 at 3:11 PM, Carla <cgourofino(at)hotmail(dot)com> wrote:

> 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
>>
>
>

--
Peter Steinheuser
psteinheuser(at)myyearbook(dot)com