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>

I'm not `Coldfusion` savvy, but to me this looks like an ambiguous reference problem. Correct me if I'm wrong... Can you alias the `licenses` table in the second query and reference it properly? See what that gets you. — SS_DBA, Aug 08 '19 at 13:37
@WEI_DBA - FWIW, the problem CF code translates to this in plain SQL `WHERE instanceId = 396B38501-blah-blah-blah-blah`. Obviously that doesn't work because the database thinks the GUID value is the name of a table column. — SOS, Aug 08 '19 at 21:27
Two points, none of which will make the error go away. One, you have to give the max(createdtime) an alias or you won't be able to do anything with it. Second, the way your QofQ is constructed, you are getting the max createdtime for each row, and there is only one value per row. That doesn't make much sense. In fact, it makes it unclear as to what you are looking to achieve. — Dan Bracuk, Aug 09 '19 at 12:32
Use cfqueryparam. Though again, it is more efficient to let the database do the aggregation and grouping. — SOS, Aug 09 '19 at 13:19
What DBMS? Using QoQ inside a loop is going to beat up on your database and network. This can very likely be done inside the SQL itself with aggregation or a window function (if your dbms supports those). — Shawn, Aug 10 '19 at 05:32

score 7 · Answer 1 · answered Aug 08 '19 at 13:40

7

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

answered Aug 08 '19 at 13:40

SOS

6,430
2
11
29

Yes I could do that, but because of the many if queries i want to make, the server performance suffers a lot. Thats why I choose to compare it in ColdFusion and store it temporarily. – dewey Aug 08 '19 at 13:45
3

Running a single query should be MORE efficient than the current code. Remember QoQ's aren't free. If you're running into performance issues your queries may be inefficient and you should look into tuning them because using QoQ's this way is just a band-aid. Better to solve the real problem. – SOS Aug 08 '19 at 13:58
1

... if you want help optimizing a query, you should open a new thread and post the SQL. Be sure to include your dbms in the question tags. – SOS Aug 08 '19 at 21:20

score 1 · Accepted Answer · edited Aug 08 '19 at 18:30

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

edited Aug 08 '19 at 18:30

Community

1
1

answered Aug 08 '19 at 13:36

scunliffe

62,582
25
126
161

Yes I could do that, but because of the many if queries i want to make, the server performance suffers a lot. Thats why I choose to compare it in ColdFusion and not by a real sql query. – dewey Aug 08 '19 at 13:43
4

If you can do it all in one query, I think that's your best bet (the DB should have the best performance) - however feel free to run a more optimized query. e.g. : https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column – scunliffe Aug 08 '19 at 13:46

Get max value of an query column

2 Answers2