pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL query help!
6+ messages / 5 participants
[nested] [flat]
* SQL query help!
@ 2002-11-23 22:09 Arcadius A. <ahouans@sh.cvut.cz>
2002-11-27 10:05 ` Re: SQL query help! Luis Sousa <llsousa@ualg.pt>
0 siblings, 1 reply; 6+ messages in thread
From: Arcadius A. @ 2002-11-23 22:09 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL query help!
2002-11-23 22:09 SQL query help! Arcadius A. <ahouans@sh.cvut.cz>
@ 2002-11-27 10:05 ` Luis Sousa <llsousa@ualg.pt>
2002-11-27 19:34 ` Re: SQL query help! Arcadius A. <ahouans@sh.cvut.cz>
0 siblings, 1 reply; 6+ messages in thread
From: Luis Sousa @ 2002-11-27 10:05 UTC (permalink / raw)
To: Arcadius A. <ahouans@sh.cvut.cz>; +Cc: 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
>
>
>
>
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL query help!
2002-11-23 22:09 SQL query help! Arcadius A. <ahouans@sh.cvut.cz>
2002-11-27 10:05 ` Re: SQL query help! Luis Sousa <llsousa@ualg.pt>
@ 2002-11-27 19:34 ` Arcadius A. <ahouans@sh.cvut.cz>
0 siblings, 0 replies; 6+ messages in thread
From: Arcadius A. @ 2002-11-27 19:34 UTC (permalink / raw)
To: pgsql-sql
Hello!
"Luis Sousa" <llsousa@ualg.pt> wrote in message
news:3DE498E4.2050002@ualg.pt...
> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* SQL query help?
@ 2005-03-07 16:22 John McGough <goff@funkster.org.uk>
2005-03-08 04:36 ` Re: SQL query help? Keith Worthington <KeithW@NarrowPathInc.com>
2005-03-08 07:06 ` Re: SQL query help? Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 6+ messages in thread
From: John McGough @ 2005-03-07 16:22 UTC (permalink / raw)
To: pgsql-sql
SELECT Count(*) FROM Work WHERE (UserID='user1' AND MAX(Finished)=0)
Work:-
+---+-------+--------+---------+----------+
| ID | JobID | UserID | Finished | Comment |
+---+-------+--------+---------+----------+
| 1 | 1 | user1 | 0 | ... |
| 2 | 1 | user1 | 1 | ... |
| 3 | 2 | user2 | 0 | ... |
| 4 | 3 | user1 | 0 | ... |
| 5 | 2 | user2 | 0 | ... |
| 6 | 2 | user1 | 1 | ... |
| 7 | 3 | user1 | 0 | ... |
+---+-------+--------+---------+----------+
All I want it to do is return the number of unfinished jobs for a specific
user.
In this example it would return 1 because job number 3 is not finished and
user1 was the last person working on it.
but I keep getting MySQL error #1111 - Invalid use of group function
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL query help?
2005-03-07 16:22 SQL query help? John McGough <goff@funkster.org.uk>
@ 2005-03-08 04:36 ` Keith Worthington <KeithW@NarrowPathInc.com>
1 sibling, 0 replies; 6+ messages in thread
From: Keith Worthington @ 2005-03-08 04:36 UTC (permalink / raw)
To: pgsql-sql; +Cc: John McGough <goff@funkster.org.uk>
John McGough wrote:
>SELECT Count(*) FROM Work WHERE (UserID='user1' AND MAX(Finished)=0)
>
>Work:-
>+---+-------+--------+---------+----------+
> | ID | JobID | UserID | Finished | Comment |
>+---+-------+--------+---------+----------+
> | 1 | 1 | user1 | 0 | ... |
> | 2 | 1 | user1 | 1 | ... |
> | 3 | 2 | user2 | 0 | ... |
> | 4 | 3 | user1 | 0 | ... |
> | 5 | 2 | user2 | 0 | ... |
> | 6 | 2 | user1 | 1 | ... |
> | 7 | 3 | user1 | 0 | ... |
>+---+-------+--------+---------+----------+
>
>All I want it to do is return the number of unfinished jobs for a specific
>user.
>
>In this example it would return 1 because job number 3 is not finished and
>user1 was the last person working on it.
>
>but I keep getting MySQL error #1111 - Invalid use of group function
>
>
John,
I may be missing something but how about
SELECT count(id) AS unfinished
FROM work
WHERE userid = 'user1'
AND finished = 0
GROUP BY jobid;
--
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL query help?
2005-03-07 16:22 SQL query help? John McGough <goff@funkster.org.uk>
@ 2005-03-08 07:06 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2005-03-08 07:06 UTC (permalink / raw)
To: John McGough <goff@funkster.org.uk>; +Cc: pgsql-sql
On Mon, Mar 07, 2005 at 04:22:15PM -0000, John McGough wrote:
> +---+-------+--------+---------+----------+
> | ID | JobID | UserID | Finished | Comment |
> +---+-------+--------+---------+----------+
This table output doesn't look like PostgreSQL's usual format.
> but I keep getting MySQL error #1111 - Invalid use of group function
And this error definitely doesn't look like something from PostgreSQL.
Are you sure you're asking the right list?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-03-08 07:06 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-23 22:09 SQL query help! Arcadius A. <ahouans@sh.cvut.cz>
2002-11-27 10:05 ` Luis Sousa <llsousa@ualg.pt>
2002-11-27 19:34 ` Arcadius A. <ahouans@sh.cvut.cz>
2005-03-07 16:22 SQL query help? John McGough <goff@funkster.org.uk>
2005-03-08 04:36 ` Re: SQL query help? Keith Worthington <KeithW@NarrowPathInc.com>
2005-03-08 07:06 ` Re: SQL query help? Michael Fuhr <mike@fuhr.org>
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