Tell me what did you try with limit and group by.
Where's IN, why don't you use EXISTS instead. It runs much master !
Regards,
Luis Sousa
Arcadius A. wrote:
>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.
>
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
>