Page 1 of 1

problem with group by query

Posted: Wed Mar 17, 2004 6:50 am
by serap
I have problems with the following query...I want to get the maxdate from the group (ref_num) and then check it against the date_sub funciton...

SELECT ref.ref_num ref_num, ref.name refname, couns.name counsname,couns.surname counssurname, max(met.meeting_date) meetdate
FROM meetings met, refs ref,counsellors couns
WHERE met.ref_cpr_num=ref.ref_cpr_num
AND couns.counsellor_id=met.counsellor_id
AND max(met.meeting_date) < (date_sub( curdate( ) , interval 90 day))
GROUP BY ref.ref_num

there is a problem with the line AND max(met.meeting_date) - is it possible to make it in a single query?

Thanks...

Posted: Wed Mar 17, 2004 7:45 am
by JAM
This code is hard to test-and-try to find issues. Can you give us the error it produces?

Posted: Wed Mar 17, 2004 8:08 am
by serap
I get the following error:

'Invalid use of group function'

and this is due to the line:

AND max( met.meeting_date ) < ( date_sub( curdate( ) , interval90

Posted: Wed Mar 17, 2004 8:38 am
by JAM
You likely need to 'GROP BY' all fields that you are selecting.
ref.ref_num (you got allready), ref.name, couns.name and so on...

But I'm not entierly sure on this (I come from a MSSql enviroment where grouping every field selected is a must in certain cases).

Continuing; Depending on SQL version, you could either use subqueries or temp tables:
http://www.mysql.com/doc/en/example-Max ... p-row.html

Good Luck...

Posted: Wed Mar 17, 2004 8:51 am
by serap
ok thanks Ill try it out...