From: "Arcadius A." <ahouans@sh.cvut.cz>
Subject: SQL query help!
Date: Sat, 23 Nov 2002 23:09:37 +0100
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 64
Message-ID: <arouav$2339$1@news.hub.org>
To: pgsql-sql@postgresql.org

Hello!

I hope that someone here could help.

I'm using PostgreSQL7.1.3

I have 3 tables in my DB: the tables are defined in the following way:


CREATE TABLE category(
id SERIAL NOT NULL PRIMARY KEY,
// etc etc

)
;

CREATE TABLE subcategory(
id SERIAL NOT NULL PRIMARY KEY,
categoryid int CONSTRAINT subcategory__ref_category
 REFERENCES category (id)
 // etc etc
)
;

CREATE TABLE entry(
entryid SERIAL NOT NULL PRIMARY KEY,
isapproved CHAR(1) NOT NULL DEFAULT 'n',
subcategoryid int CONSTRAINT entry__ref_subcategory
 REFERENCES subcategory (id)
 // atd
,
)
;


I have the following SQL query :

 "SELECT * FROM entry where isapproved='y'  AND  subcategoryid IN (SELECT id
FROM subcategory WHERE
categoryid='"+catID+"') ORDER BY subcategoryid DESC";


For a given categoryid( catID), the query will return all entries in the
"entry" table
having a corresponding subcategoryid(s)[returned by the inned subquery].

But I want to return only a limited number of entries of each
subcategory..... let's say that I want to return at most 5 entries of  each
subcategory type ( for instance if the inner subquery returns 3 results,
thus I will be having in total at most 15 entries as relust)....

How can this be achieved?

I'm aware of postgreSQL "LIMIT" and "GROUP BY" clause..... but so far, I'm
not able to put all this together...

Thanks in advance.

Arcadius.