problem with group by query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
serap
Forum Commoner
Posts: 31
Joined: Thu Jan 15, 2004 5:10 pm

problem with group by query

Post 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...
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

This code is hard to test-and-try to find issues. Can you give us the error it produces?
serap
Forum Commoner
Posts: 31
Joined: Thu Jan 15, 2004 5:10 pm

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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...
serap
Forum Commoner
Posts: 31
Joined: Thu Jan 15, 2004 5:10 pm

Post by serap »

ok thanks Ill try it out...
Post Reply