Put the min clause in a having statement ex. select * from words group by count having min(count) > 50;
-----Original Message----- From: David Hugh-Jones [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 23, 2001 4:25 PM To: Haapanen, Tom Cc: [EMAIL PROTECTED] Subject: RE: selecting rows where all rows meet a criterion Alas, my version won't let me use min() in a where clause - it only seems to let me use it in the select clause. Is there a workaround? mysql> select * from words where min(count) > 50 group by count; ERROR 1111: Invalid use of group function d On Tuesday 23 October 2001 8:17 am, Haapanen, Tom wrote: > This might work ... but I don't have a suitable database to test with ... > > select * from students,grades > where students.id=grades.id > and min(grades.grade - students.average_grade) > 0 > group by students.id > > > Tom Haapanen > [EMAIL PROTECTED] > > > -----Original Message----- > From: David Hugh-Jones [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, 23 October, 2001 13:10 > To: [EMAIL PROTECTED] > Subject: selecting rows where all rows meet a criterion > > > > OK, I have a tricky SQL syntax problem I would like to share. > > For simplicity's sake, I'll use an analogy. I have two tables, of students > and grades, each with a "ID" column for use in joins, corresponding to a > student's ID. > > The student table has an "average grade" column which shows that student's > average grade last year. The "grades" table has a column for subject (e.g. > English, Math) and a column for the grade achieved. > > Suppose I want to select all my students who have performed very well this > year. I want to select all the students who have beaten last year's average > in ALL of this years marks! > > SELECT * FROM students,grades WHERE students.ID=grades.ID AND grades.grade > > > > students.average_grade > > This doesn't work. It gets me all the occasions where a student has beaten > his average. But I only want students who have never gone below their > average! In other words, I want to select from the students table, based on > information on multiple rows in the grades table, where ALL the grades rows > need to fulfil a certain criterion. > > Help would be gratefully received! -- David Hugh-Jones http://www.zaygo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
