mysql MAX-GROUP BY

[Anon]

I am trying to get the folowing sql statement to return only the max date for each of the types.

As you can it returns the same type twice.

select Type, MAX(Datex) as maxDatex from tblResponses
where Type like 'QAC%'
group by Datex,Type
order by Datex Desc

Results
TYPE Datex
QAC Azo-Brader 05/30/2001

QACP 05/30/2001
QAC Azo-Brader 05/29/2001

[meddie]

It looks as though the "Datex" in the group by clause is the problem. What it's doing is grouping by Type AND Date, which means it'll see QAC Azo-Brader 5/30/2001 as different from QAC Azo-Brader 5/29/2001. Just use "group by Type" and you should be fine.

[ace21]

If I don't include both I get this error

"You tried to execute a query that does not include the specified expression 'Datex' as part of an aggregate function."

Meddie wrote:

It looks as though the "Datex" in the group by clause is the problem. What it's doing is grouping by Type AND Date, which means it'll see QAC Azo-Brader 5/30/2001 as different from QAC Azo-Brader 5/29/2001. Just use "group by Type" and you should be fine.

[meddie]

Sorry. You also have to change the "order by" to use Max(Datex) or its alias instead of just Datex. Doing that would put Datex in the aggregate function the error mentioned.

David wrote:

If I don't include both I get this error

"You tried to execute a query that does not include the specified expression 'Datex' as part of an aggregate function."

Meddie wrote:

It looks as though the "Datex" in the group by clause is the problem. What it's doing is grouping by Type AND Date, which means it'll see QAC Azo-Brader 5/30/2001 as different from QAC Azo-Brader 5/29/2001. Just use "group by Type" and you should be fine.

[ace21]

Thanks for your help. For those who care here is the statement that works.

select Type, MAX(Datex) as maxDatex from tblResponses
where Type like 'QAC%'
group by Type
order by MAX(Datex)

Ace21