Can I get a query row by index in ColdFusion?

Question

I want to get a specific row in a ColdFusion Query object without looping over it.

I'd like to do something like this:

<cfquery name="QueryName" datasource="ds">
SELECT *
FROM    tablename
</cfquery>

<cfset x = QueryName[5]>

But it's giving me an error saying that the query isn't indexable by "5". I know for a fact that there are more than 5 records in this query.

Answer 1

I think there is a simpler solution... I am guessing you know your column names and only want this column or that one. Then you don't need to put the whole row in a struct. You can reference the query by row number (remember its 1 based not 0).

yourQueryName["yourColumnName"][rowNumber]

<cfoutput>
     #mycontacts["Name"][13]#
     #mycontacts["HomePhone"][13]# 
</cfoutput>

Answer 2

Check out the documentation for queryGetRow. It accepts a query object and an index of the row with the first row being referenced with the index of 1 (NOT 0) The index used this way is required to be a positive integer.

<cfquery name="QueryName" datasource="ds">
  SELECT *
  FROM tablename
</cfquery>

<!---
    This would retrieve the first record of the query
    and store the record in a struct format in the variable 'x'.
--->
<cfset x = queryGetRow(QueryName, 1) />
<!---
    This is an alternative using the member method form of queryGetRow
--->
<cfset x = QueryName.getRow(1) />