SELECT TOP in query of queries?

[theotrain]

i have a query of queries in coldfusion and i want to limit the results to a particular quantity.

ive read you can use

SELECT TOP 10 * FROM myTable ...

and that works for me in a query, but blows up when i try it in a query of queries for some reason. heres the code

<cfquery name="sortDistance" dbtype="query">
SELECT top 10 * from joinTables WHERE distance < #radius#
ORDER BY distance
</cfquery>

can anybody suggest an alternative way of achieving the same result? id like to limit the size of the query result itself and not simply display only part of the records.

thanks!

 

[r937]

TOP blows up in QofQ because it isn't supported (it's not standard sql)

have you tried the MAXROWS parameter of the CFQUERY tag?



r937.com | rudy.ca

 

[theotrain]

im trying to avoid using maxrows, i would rather actually limit the size of the recordset in the query. im beginning to think now that my logic may be flawed though.

i was thinking that using maxrows is inefficient because its better to return a smaller recordset right from the query, and while i suppose thats normally true, i guess with a query of queries it doesnt really matter because you've already fetched a big-ass recordset from the db and how you dissect it from there isnt really going to matter much.

is that true?

 

[r937]

yes, that's an accurate assessment

if you've already retrieved two, um, large result sets, using MAXROWS in query of query is fine

r937.com | rudy.ca