First Mathematical Function

[ssailer]

I'm stumped. I am filtering a query and I need to be able to use a First function in ColdFusion, like in Access.

This is what my query is in Access:
SELECT First(query2.Latitude) AS Latitude, First(query2.Longitude) AS Longitude
FROM query2

This is what I need my query to be in ColdFusion:

<cfquery name="getAVG" dbtype="query">
SELECT min(passedzip.Latitude) AS latitude, MIN(passedzip.Longitude) AS longitude
FROM passedzip
</cfquery>

Of course, ColdFusion doesn't support the First function and it's really screwing me up. Min doesn't work - I need First.

Does anybody out there have a clue as to how to work around this? I am using a query of queries in my site to get the results I need.

Thank you!

[Newsgroup_User]

ssailer wrote:

ColdFusion does not need to support First() it does not parse SQL, it
just bundles it up and sends it to the database. If your database
understands first() then just send it the SQL code you need.

[tclaremont]

How about using TOP 1?
How about setting MaxRows equal to 1?

[ssailer]

Top 1 would work if query of query supported it, but it doesn't - I get errors. Also, I can't include the first() function in the sql because the query needs to be a query of queries. I desperately need the FIRST() function to work in C.F.

[insuractive]

How about just using #QueryName.ColumnName# outside of a <cfoutput query=""> or <cfloop query=""> loop. It will always return the first row in your query.

i.e.

<cfset Latitude = passedzip.Latitude>
<cfset Longitude = passedzip.Longitude>

[Newsgroup_User]

How about just using #QueryName.ColumnName# outside of a <cfoutput
query=""> or <cfloop query=""> loop. It will always return the first
row in your query.

Or more properly... #queryName.ColumnName[1]# which is what the above is
a short cut to though this allows one to get the second row if they
desire, ex. #queryName.ColumnName[2]#. And of course if you replace the
integer in the row designation with a variable containing an integer,
then all kinds of fun things can be done.

[ssailer]

I actually got what I needed using a couple of different queries. Thanks to everybody who responded!

[fluid]

i know this has been answered, but i would suggest putting as much of this on the database server as possible. use subqueries...id never suggest querying a query in coldfusion like that. it is handy, and a super shortcut..but will never really be that beneficial to you in the long run...

[ssailer]

Thanks, fluid. I actually was able to do away with the query of queries and let the SQL do most of the work, as you mentioned. It is actually working quite well now - what a relief!