SELECT TOP And ColdFusion Query Of Queries
Posted November 28, 2006 at 5:00 PM by Ben Nadel
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.
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
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.
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
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.
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!!
Mark, good stuff!
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
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.
@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).
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.
@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.
Just wanted to say thanks for the website. This is probably the one hundredth time it has saved me hours of searching.
@Brian,
Always glad to help, or at least to provide a platform for some great conversation.
Oh yeah! MAXROWS!
Now why didn't I think of that?
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).
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>
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)>
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
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
