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;
2011/1/11 Peter Steinheuser
> 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;
> cat
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" :> 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? He
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
+---
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
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 magazinecatego
