Get ... WHERE MAX(column) .... using a LEFT JOIN

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
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Get ... WHERE MAX(column) .... using a LEFT JOIN

Post by mikebr »

I have been using a left join to get some data from a MySQL table using PHP without problems, but now the 'service_take' column will have more than one entry. The table has a 'serviced_date' column which has the date entered as a date stamp (e.g. 1226882280).

I just want to return the last entry and have tried using my altered join as follows:

Code: Select all

$the_qry = ("SELECT tl.tl_id, tl.tl_name, tl.tl_contact, tl.tl_town, st.st_counter_end, st.st_split "
        . "FROM table_locations AS tl LEFT JOIN service_take AS st ON (tl.tl_id = st.st_tl_id) WHERE MAX(st.st_serviced_date)");
and as

Code: Select all

 
$the_qry = ("SELECT tl.tl_id, tl.tl_name, tl.tl_contact, tl.tl_town, st.st_counter_end, st.st_split "
        . "FROM table_locations AS tl LEFT JOIN service_take AS st ON (tl.tl_id = st.st_tl_id) WHERE st.st_serviced_date = MAX(st.st_serviced_date)");
but get 'Invalid use of group function' errors, anyone any idea where I am going wrong here?

Thanks in advance
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Get ... WHERE MAX(column) .... using a LEFT JOIN

Post by Eran »

MAX() is an aggregate function which means it can only be used with a GROUP BY clause. Check out this recent thread which asked a similar question - viewtopic.php?f=2&t=95643
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Re: Get ... WHERE MAX(column) .... using a LEFT JOIN

Post by mikebr »

pytrin wrote:MAX() is an aggregate function which means it can only be used with a GROUP BY clause. Check out this recent thread which asked a similar question - viewtopic.php?f=2&t=95643
Thanks for the pointer pytrin but can't seem to get the results i'm looking for.... my query is now structured as:

Code: Select all

$the_qry = ("SELECT MAX(st.st_serviced_date) AS `st_serviced_date`, tl.tl_id, tl.tl_name, tl.tl_contact, tl.tl_town, st.st_counter_end, st.st_split "
        . "FROM `table_locations` AS tl LEFT JOIN `service_take` AS st ON (tl.tl_id = st.st_tl_id) WHERE st.st_serviced_date = `st_serviced_date` GROUP BY `st_t_id`");
but this does not return the MAX column value from `st_serviced_date`, seems to return the first entry for each entry of the `st_serviced_date`.

Maybe the best way forward on this would be separate queries..... thanks for the input anyway.
Post Reply