MAX Function - Subquery?

[mrobinson83]

Hi,

 

I am having problems with the following query:

 

SELECT status_lead.lead_id, status_lead.status_id, MAX(status_lead.status_version), lead.Date_Logged
FROM status_lead
JOIN lead ON status_lead.lead_ID = lead.lead_ID
WHERE status_lead.status_id =3
AND
lead.Date_Logged BETWEEN DATE_SUB( CURDATE( ) , INTERVAL 2 week ) 
AND DATE_SUB( CURDATE( ) , INTERVAL 1 week ) 
GROUP BY status_lead.lead_id

 

The query works but it doesn't seem to apply the "status_lead.status_id =3" part of the statement. The output always seem to show the correct max value for a unique lead however status_id = 1 and not 3 in most cases.

 

I am querying to find the max value of status_version whereby status_id=3, I hope this makes sense. I am pretty sure I am missing some brackets or something somewhere.

 

Thanks in advance!

[mrobinson83]

MYSQL Version - 5.1.11

 

 

[fenway]

You've getting funny results because you're including non-group by columns in your select list..that's a no-no.