CODEWITHSUNDEEP
sqlcoldfusionqoq
Konstantin Schütte
Konstantin Schütte

Reputation: 1009

Get max value of an query column

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: enter image description here

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>

Upvotes: 0

Views: 768

Answers (2)

scunliffe
scunliffe

Reputation: 63588

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

Upvotes: 1

SOS
SOS

Reputation: 6550

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

Upvotes: 7

Related Questions