Skip to main content
Ben Nadel at NCDevCon 2016 (Raleigh, NC) with: Ray Camden
Ben Nadel at NCDevCon 2016 (Raleigh, NC) with: Ray Camden ( @cfjedimaster )

SELECT TOP And ColdFusion Query Of Queries

By on
Tags: ,

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.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/go/417

Reader Comments

9 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

15,688 Comments

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.

9 Comments

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

15,688 Comments

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.

2 Comments

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!!

2 Comments

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

1 Comments

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.

15,688 Comments

@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).

9 Comments

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.

15,688 Comments

@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.

4 Comments

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).

1 Comments

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>
4 Comments

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)>

4 Comments

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

4 Comments

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

Oh my chickens, this post is old!
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel