Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2008 (Washington, D.C.) with:

SELECT TOP And ColdFusion Query Of Queries

By Ben Nadel on
Tags: ColdFusion, SQL

NOTE: Please disregard the following post. It is solved using a CFQuery Attribute, MAXROWS, that I didn't know about. (Thanks Joe!)... you think you know a language and them BLAM! You don't.

Using the SELECT TOP directive of a SQL statement is a nice way to limit the number of records that are returned from the database. Unfortunately (as far as I can see), ColdFusion Query of Queries (QoQ) do NOT support the SELECT TOP directive. There is, however, a fairly easy way to mimic this using the Java methods of the ColdFusion query record set. This is not officially supported:

  • <!--- Create movie query. --->
  • <cfset qMovie = QueryNew(
  • "name",
  • "CF_SQL_VARCHAR"
  • ) />
  •  
  • <!--- Add movie rows. --->
  • <cfset QueryAddRow( qMovie, 10 ) />
  •  
  • <!--- Set query data. --->
  • <cfset qMovie[ "name" ][ 1 ] = "Terminator" />
  • <cfset qMovie[ "name" ][ 2 ] = "Terminator 2" />
  • <cfset qMovie[ "name" ][ 3 ] = "Terminator 3" />
  • <cfset qMovie[ "name" ][ 4 ] = "Predator" />
  • <cfset qMovie[ "name" ][ 5 ] = "Twins" />
  • <cfset qMovie[ "name" ][ 6 ] = "Read Head" />
  • <cfset qMovie[ "name" ][ 7 ] = "True Lies" />
  • <cfset qMovie[ "name" ][ 8 ] = "Eraser" />
  • <cfset qMovie[ "name" ][ 9 ] = "Kindergarten Cop" />
  • <cfset qMovie[ "name" ][ 10 ] = "Pumping Iron" />
  •  
  • <!--- Select the movies. --->
  • <cfquery name="qSubMovie" dbtype="query">
  • SELECT
  • name
  • FROM
  • qMovie
  • ORDER BY
  • name ASC
  • </cfquery>
  •  
  •  
  • <!---
  • Get the TOP 3 movies. We only need to do this if the
  • record set is GREATER than 3 records long.
  • --->
  • <cfif (qSubMovie.RecordCount GT 3)>
  •  
  • <!---
  • Remove all rows beyond 3 (index 3 and
  • greater from the Java stand point).
  • --->
  • <cfset qSubMovie.RemoveRows(
  • JavaCast( "int", 3 ),
  • JavaCast( "int", qSubMovie.RecordCount - 3 )
  • ) />
  •  
  • </cfif>

If you are not comfortable with this, you could manually build the second query and then add the rows, but that is pain. And, to get that to work, you have to run a Query of Queries anyway in order to get any WHERE or ORDER criteria to work. At that point, why go through the hassle of doing any more manual work than in necessary.

If you do go this way, you might want to hide the implementation into a user defined function such as QueryTrim():

  • <cffunction
  • name="QueryTrim"
  • access="public"
  • returntype="query"
  • output="false"
  • hint="Trims a query to the number of rows requested.">
  •  
  • <!--- Define arguments. --->
  • <cfargument name="Query" type="query" required="true" />
  • <cfargument name="RecordCount" type="numeric" required="true" />
  •  
  • <!--- Check to see if we have rows to remove. --->
  • <cfif (ARGUMENTS.Query.RecordCount GT ARGUMENTS.RecordCount)>
  •  
  • <!--- Trim the recordset. --->
  • <cfset ARGUMENTS.Query.RemoveRows(
  • JavaCast( "int", ARGUMENTS.RecordCount ),
  • JavaCast( "int", (ARGUMENTS.Query.RecordCount - ARGUMENTS.RecordCount) )
  • ) />
  •  
  • </cfif>
  •  
  • <!---
  • Return the updated query. This is NOT required as
  • the query object is passed by reference, not by value.
  • However, this would be useful to do as it allows for
  • method chaining.
  • --->
  • <cfreturn ARGUMENTS.Query />
  • </cffunction>

You would then call this as follows:

  • <!--- Trim query. --->
  • <cfset QueryTrim(
  • qSubMovie,
  • 3
  • ) />

Not only is this perhaps easier to read and less work, it allows you to change the implementation of the way the SELECT TOP mimicry works. Personally, I think it would be great for them to just build SELECT TOP into the ColdFusion query of queries (how hard could it be?). But, until then, this has worked very nicely for me.

Tweet This Groovy post by @BenNadel - SELECT TOP And ColdFusion Query Of Queries Thanks my man — you rock the party that rocks the body!


Reader Comments

Hey Ben,

Is there a reason why you'd do this and not use MAXROWS on the QoQ, such as:

# <cfquery name="qSubMovie" dbtype="query" maxrows="10">
# SELECT
# name
# FROM
# qMovie
# ORDER BY
# name ASC
# </cfquery>

-Joe

Reply to this Comment

Yeah, cause I don't think I even knew there was a MAXROWS attribute ;)

Thanks Joe! Man, I wish knew all the ins an outs of the tags.

Reply to this Comment

Heheh...no worries! I can't tell you how long I used replaceNoCase(someList, ",", "','", "all") before I realized ListQualify() existed.

My rule of thumb for CF: if there's a common task that you'd like to implement code to accomplish, it's probably in the library.

-Joe

Reply to this Comment

Yeah, ColdFusion is so freakin' cool. I just never needed to use MAXROWS before (that I can remember) for standard queries as I could always just use TOP. This gives me something nice to bring up in next week's Staff Meeting (yeah, I like to share the knowledge).

On an unrelated topic, I am reading your article on Model-Glue Fundamentals in the Fushion Authority Quarterly. Very nice. So was the article on Mach-ii by Matt Woodward. The both of you have made these frameworks much clearer. I have looked at them before online, but for some reason never could bridge some knowledge gap. These articles, somehow, got me there. I get it now :) Not that I understand it all, but some things finally clicked. Maybe its the pressure of having to have it ALL in one small article or something... I don't know what it is, but the examples and explanations are just really nice. So, thanks.

Reply to this Comment

Ben:

Although it might be unnecessary for what you had in mind, this worked perfectly for something I was working on today! I am taking several similar queries and joining them using a ColdFusion query of queries UNION. But since I cannot do a TOP in the SELECT statement, I used the querytrim() function to remove excess rows from the query before running it through the QoQ.

Thanks!!

Reply to this Comment

Thank you for the post - and the answer! I was encountering the exact same issue, and didn't even think to look at maxrows!

Danke, dahling!

Best wishes,
Cat

Reply to this Comment

Why don't you use your SQL statement, this is much more flexible

SQL:
select TOP [3] {*} from tbl_name

The number in the brackets [ ] can be any number, and you can either select { * } or replace it with specific column names and other SQL.

Reply to this Comment

@Eric,

Agreed! However, this would be useful (MAXROWS) when you have working with an existing queries (ex. one that comes back from a service object).

Reply to this Comment

I would like to see TOP added to QoQ. Today I am working on getting at least 5 search results in a search query. If we return less than that, I remove some of the users criteria and search again. But I want to hang on to the 2 (or however many) results that DID come back with too-restictive search criteria.

My plan was to use QoQ to UNION it all together, but since I can't say TOP 3 in the second, less restrictive query (to equal 5 total), I don't think this will work.

I could put them all together and THEN limit it using maxrows, but I'm not sure if the ordering will be correct.

Reply to this Comment

@Ryan,

I would suggest the MaxRows technique with the UNION. I cannot be certain, but I would bet that the order you are looking for will be retained. I would give it a test before you discount it.

Reply to this Comment

Pretty old post, just wanted to point out that the maxrows attribute should never ever be used except in QoQ as it causes a *huge* overhead (as coldfusion is known to be one huge mess on the back-end).

Reply to this Comment

I have a repetitive task that will crash without scoping everything. How can I change the starting row? For instance, if I could use limit in a QoQ it would look like this:

  • <cfloop from="0" to="#n-1#" index="i">
  • <cfquery name="get_500_at_a_time" dbtype="query">
  • SELECT * FROM table LIMIT (#i#*500), 500
  • </cfquery>
  • </cfloop>

Reply to this Comment

The following works as I wanted. But only for the FIRST OrgID (the CFOUTPUT proves there are 2).

Any ideas?

best, paul

<!--- Create arrays. --->
<cfset ID_Array2 = ArrayNew(1)>

<!--- Make a query. --->
<cfset GetListingsInOneClass2 = QueryNew("")>

<cfset IDD="0">
<cfloop LIST="#ValueList(GetDistinctOrgIDsInOneClass.OrgID)#" INDEX="IJK">
<cfset IDD=IDD+1>

<cfset IJKL = GetDistinctOrgIDsInOneClass.OrgID[#IDD#] >

<cfoutput>#IJKL#</cfoutput><br><br>

<cfquery name="qSub" dbtype="query" maxrows="1" >
SELECT ID
FROM GetListingsInOneClass
WHERE OrgID = #IJKL#
ORDER BY DateOfData DESC
</cfquery>

<cfset ID_Array2[#IDD#] = qSub.ID[IDD] >

</cfloop>

<cfset nColumnNumber1 = QueryAddColumn(GetListingsInOneClass2, "ID",
"Integer", ID_Array2)>

Reply to this Comment

The above app is for Admin purposes only, not for the general public. So it doesn't have to be efficient.

I'm thinking the easy way, code-wise, might be to put the data in a SQL Server Table.

Then one query could do the job.

best, paul

Reply to this Comment

I found the problem in the above.

<cfset ID_Array2[#IDD#] = qSub.ID[IDD] >

should have been:

<cfset ID_Array2[#IDD#] = qSub.ID >

best, paul

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.