Re: Getting top 2 by Category
| От | Ozer, Pam |
|---|---|
| Тема | Re: Getting top 2 by Category |
| Дата | |
| Msg-id | 216FFB77CBFAEE4B8EE4DF0A939FF1D1018442@mail-001.corp.automotive.com обсуждение исходный текст |
| Ответ на | Getting top 2 by Category ("Ozer, Pam" <pozer@automotive.com>) |
| Ответы |
Function To Strip HTML
|
| Список | 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
В списке pgsql-sql по дате отправления: