From: | Luis Sousa <llsousa(at)ualg(dot)pt> |
---|---|
To: | "Arcadius A(dot)" <ahouans(at)sh(dot)cvut(dot)cz> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL query help! |
Date: | 2002-11-27 10:05:24 |
Message-ID: | 3DE498E4.2050002@ualg.pt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers pgsql-sql |
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
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Christian Imbeault | 2002-11-27 10:16:02 | Re: 7.3RC2 createlang error |
Previous Message | Erwan DUROSELLE | 2002-11-27 09:28:29 | Rp. : Mailing list question |
From | Date | Subject | |
---|---|---|---|
Next Message | david luo | 2002-11-27 10:24:42 | How can i import database from MSSQL to Postgres?(NULL BODY) |
Previous Message | Teodor Sigaev | 2002-11-27 09:16:27 | Re: contrib/ltree patches |
From | Date | Subject | |
---|---|---|---|
Next Message | Tilo Schwarz | 2002-11-27 10:13:40 | Re: Question on SQL and pg_-tables |
Previous Message | Achilleus Mantzios | 2002-11-27 08:00:37 | Re: SQL -select count- |