From: Luis Sousa <llsousa@ualg.pt>
To: Arcadius A. <ahouans@sh.cvut.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL query help!
Date: Wed, 27 Nov 2002 10:05:24 +0000
Message-ID: <3DE498E4.2050002@ualg.pt> (raw)
References: <arouav$2339$1@news.hub.org>
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
>
>
>
>
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: llsousa@ualg.pt, ahouans@sh.cvut.cz
Subject: Re: SQL query help!
In-Reply-To: <3DE498E4.2050002@ualg.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox