where max

[Guest]

gday just have a small problem.

i need to have a select statement like below, but this gives me an error, any ideas how to do this

so what i need is to select all where the number matches and then select the last record of this number

<CFQUERY datasource="#application.ds#" Name="Show">
SELECT *
FROM sms_records
WHERE NumberSent = '421365488' AND ID = Max(ID)
</cfquery>

[Dan_Bracuk]

put the max(id) into a subquery.

[Guest · Accepted solution]

ok thanks, I have done that but it returns every id in the table? i just need it to select the last id number

<CFQUERY datasource="#application.ds#" Name="Show66">
SELECT *
FROM sms_records
WHERE NumberSent = '421365488' AND ID = (SELECT MAX(ID)AS ID FROM sms_records GROUP BY ID)
</cfquery>

[Dan_Bracuk]

Take away the group by clause in the subquery.