How to use Query of Queries as subquery (Coldfusion)

Question

I want to use a query of queries as a subquery but i get a syntax error: Encountered "(. Here is my query (qHistoryData is my query object):

<cfquery dbtype="query">
    SELECT *
    FROM (
        SELECT 
            t2.*,
            ROW_NUMBER() OVER(
                PARTITION BY collectid
                ORDER BY update_on DESC
            ) AS seqnum
        FROM qHistoryData t2
    ) t
    WHERE t.seqnum = 1;
</cfquery>

Answer 1

A Query of Queries is implemented entirely at the ColdFusion application layer (in Java) and does not involve the database so you cannot use many of the functions that are available in the database.

Add a column to your qHistoryData query that calculates ROW_NUMBER() OVER( PARTITION BY collectid ORDER BY update_on DESC ) AS seqnum and then in your Query of Queries you can do:

<cfquery dbtype = "query">
SELECT *
FROM   qHistoryData
WHERE  seqnum = 1;
</cfquery>

Your other option is to manually process the query object and remove the unwanted rows.