[SQL] SQL query help!
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
[SQL] LIMIT and/or GROUP BY help!
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL query help!
Hello! "Luis Sousa" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > This is a cryptographically signed message in MIME format. > > --ms080209060900030807050408 > Content-Type: text/plain; charset=us-ascii; format=flowed > Content-Transfer-Encoding: 7bit > > 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 ! > Thanks for the reply! Alright, I'll use EXISTS instead of IN I didn't know that EXISTS is faster. About my query, I have tried : " SELECT * FROM entry where isapproved='y' AND EXISTS (SELECT id FROM subcategory WHERE catid='2') ORDER BY subcatid DESC LIMIT 5; "; This will return only 5 rows But when I add the GROUP BY, then I got error " SELECT * FROM entry where isapproved='y' AND EXISTS (SELECT id FROM subcategory WHERE catid='2') ORDER BY subcatid DESC LIMIT 5 GROUP BY subcatid; " : ERROR: parser: parse error at or near "GROUP" Thanks. Arcadius. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
