pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting top 2 by Category
6+ messages / 4 participants
[nested] [flat]
* Getting top 2 by Category
@ 2011-01-11 19:00 Ozer, Pam <pozer@automotive.com>
2011-01-11 19:51 ` Re: Getting top 2 by Category Peter Steinheuser <psteinheuser@myyearbook.com>
2011-01-11 20:00 ` Re: Getting top 2 by Category msi77 <msi77@yandex.ru>
0 siblings, 2 replies; 6+ messages in thread
From: Ozer, Pam @ 2011-01-11 19:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting top 2 by Category
2011-01-11 19:00 Getting top 2 by Category Ozer, Pam <pozer@automotive.com>
@ 2011-01-11 19:51 ` Peter Steinheuser <psteinheuser@myyearbook.com>
2011-01-11 19:53 ` Re: Getting top 2 by Category Ozer, Pam <pozer@automotive.com>
2011-01-19 20:11 ` Re: Getting top 2 by Category Carla <cgourofino@hotmail.com>
1 sibling, 2 replies; 6+ messages in thread
From: Peter Steinheuser @ 2011-01-11 19:51 UTC (permalink / raw)
To: Ozer, Pam <pozer@automotive.com>; +Cc: 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@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
psteinheuser@myyearbook.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting top 2 by Category
2011-01-11 19:00 Getting top 2 by Category Ozer, Pam <pozer@automotive.com>
2011-01-11 19:51 ` Re: Getting top 2 by Category Peter Steinheuser <psteinheuser@myyearbook.com>
@ 2011-01-11 19:53 ` Ozer, Pam <pozer@automotive.com>
1 sibling, 0 replies; 6+ messages in thread
From: Ozer, Pam @ 2011-01-11 19:53 UTC (permalink / raw)
To: Peter Steinheuser <psteinheuser@myyearbook.com>; +Cc: pgsql-sql
Perfect. Thank You. I knew there had to be something simple.
From: Peter Steinheuser [mailto:psteinheuser@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 <pozer@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
psteinheuser@myyearbook.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting top 2 by Category
2011-01-11 19:00 Getting top 2 by Category Ozer, Pam <pozer@automotive.com>
2011-01-11 19:51 ` Re: Getting top 2 by Category Peter Steinheuser <psteinheuser@myyearbook.com>
@ 2011-01-19 20:11 ` Carla <cgourofino@hotmail.com>
2011-01-19 20:59 ` Re: Getting top 2 by Category Peter Steinheuser <psteinheuser@myyearbook.com>
1 sibling, 1 reply; 6+ messages in thread
From: Carla @ 2011-01-19 20:11 UTC (permalink / raw)
To: Peter Steinheuser <psteinheuser@myyearbook.com>; pgsql-sql
2011/1/11 Peter Steinheuser <psteinheuser@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 <pozer@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
> psteinheuser@myyearbook.com
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting top 2 by Category
2011-01-11 19:00 Getting top 2 by Category Ozer, Pam <pozer@automotive.com>
2011-01-11 19:51 ` Re: Getting top 2 by Category Peter Steinheuser <psteinheuser@myyearbook.com>
2011-01-19 20:11 ` Re: Getting top 2 by Category Carla <cgourofino@hotmail.com>
@ 2011-01-19 20:59 ` Peter Steinheuser <psteinheuser@myyearbook.com>
0 siblings, 0 replies; 6+ messages in thread
From: Peter Steinheuser @ 2011-01-19 20:59 UTC (permalink / raw)
To: cgourofino@hotmail.com; +Cc: pgsql-sql
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@hotmail.com> wrote:
> 2011/1/11 Peter Steinheuser <psteinheuser@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 <pozer@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
>> psteinheuser@myyearbook.com
>>
>
>
--
Peter Steinheuser
psteinheuser@myyearbook.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting top 2 by Category
2011-01-11 19:00 Getting top 2 by Category Ozer, Pam <pozer@automotive.com>
@ 2011-01-11 20:00 ` msi77 <msi77@yandex.ru>
1 sibling, 0 replies; 6+ messages in thread
From: msi77 @ 2011-01-11 20:00 UTC (permalink / raw)
To: Ozer, Pam <pozer@automotive.com>; pgsql-sql
There some ways to do this in one query. Look here:
http://www.sql-ex.ru/help/select16.php
---------------
11.01.11, 22:00, "Ozer, Pam" <pozer@automotive.com>:> 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 areCategoryID MagazineID3 23 84 104 11 Pam Ozer>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-01-19 20:59 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-11 19:00 Getting top 2 by Category Ozer, Pam <pozer@automotive.com>
2011-01-11 19:51 ` Peter Steinheuser <psteinheuser@myyearbook.com>
2011-01-11 19:53 ` Ozer, Pam <pozer@automotive.com>
2011-01-19 20:11 ` Carla <cgourofino@hotmail.com>
2011-01-19 20:59 ` Peter Steinheuser <psteinheuser@myyearbook.com>
2011-01-11 20:00 ` msi77 <msi77@yandex.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox