Get max value of an query column

Question

i am trying to loop through a query, to get the highest createdTime. The column instanceId is a GUID (string) what gives me this error:

The reference 396B3850 is the beginning of an instanceId

<cfquery datasource="hostmanager" name="licenses">
SELECT *
FROM licenses
</cfquery>

<cfloop query="licenses">
    <cfquery name="getHighestCreatedTime" dbtype="query">
    SELECT MAX(CREATEDTIME)
    FROM licenses
    WHERE instanceId = #licenses.instanceId#
    AND startDate = #licenses.startDate#
    </cfquery>
</cfloop>

Answer 1

I'm not too familiar with ColdFusion, but can you not just run a query for the max value? For example

SELECT TOP 1 CREATEDTIME 
FROM   licenses 
WHERE  {any conditions you want} 
ORDER BY CREATEDTIME DESC

Answer 2

The reason for the error is that string literals like #licenses.instanceId# must be enclosed in quotes - or better yet - wrapped in cfqueryparam.

However, querying inside a loop is extremely inefficient, and it's not needed anyway. Just use your database's aggregate functions:

SELECT  InstanceID, startDate, MAX(CreatedTime) AS MaxCreatedTime
FROM    licenses 
GROUP BY InstanceID, startDate