To: pgsql-sql@postgresql.org
Path: not-for-mail
From: "Arcadius A." <ahouans@sh.cvut.cz>
Subject: LIMIT and/or GROUP BY help!
Date: Sat, 23 Nov 2002 13:33:03 +0100
Lines: 37
Message-ID: <arnse6$iia$1@main.gmane.org>
NNTP-Posting-Host: viking.sh.cvut.cz
NNTP-Posting-Date: Sat, 23 Nov 2002 12:31:03 +0000 (UTC)

Hello!

 I hope that someone here could help.

I'm using PostgreSQL7.1.3

I have 3 tables: entry, subcategory and category.

The table "entry" has a foreign key "subcategoryid" with reference to the
table "subcategory", and the "subcategory" table has a foreign key
"categoryid" with reference to the table "category"

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, this will return all entries in the "entry" table
having a corresponding subcategoryid(s).

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

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.