• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

Receiving error on ColdFusion query of query

I have a query and a query of that query, something like the below, but I am receiving the following error. Could someone assist?

Query Of Queries syntax error.Encountered "1. Incorrect Select Statement, Expecting a 'FROM', but encountered '1' instead, A select statement should have a 'FROM' construct.

<cfquery name="firstQ" datasource="myds"  username="user1" password="pass1">
select ID, Column1, Column2
From My table
where ...
</cfquery>

<cfquery name="topfirstQ" dbtype="query">
select top 1 ID
From firstQ
</cfquery>
0
earwig75
Asked:
earwig75
  • 3
  • 2
2 Solutions
 
gdemariaCommented:
remove "TOP 1" from the select (not allowed in QofQ)

Instead use maxRows in the parmeter

<cfquery name="topfirstQ" dbtype="query" maxRows =1>
   select  ID
     From firstQ
</cfquery>


Of course, there is no need for this at all, because to get the ID of the first record, you just take it...

<cfquery name="firstQ" datasource="myds"  username="user1" password="pass1">
select ID, Column1, Column2
From My table
where ...
</cfquery>
<cfset firstID = firstQ.ID>

Or you can use...

<cfset firstID = firstQ["ID"][1]>
0
 
_agx_Commented:
(no points, gdemaria already provided the answer ...)

Just FYI, if you were going to grab the top 1 record/maxrRows=1, you must ORDER the results by something - an "ID", date, etc.... or else you're just grabbing an arbitrary record that isn't necessarily the one you want.
0
 
earwig75Author Commented:
Forgive me. I left something out of my initial question. I don't actually need the first record from the first query. I need the LAST one. In my real code I am trying to select top 1 and then: order by ID DESC

The first query gets a bunch of records, and then the 2nd query, I want the last record from that first one.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
gdemariaCommented:
To get the last one, you can do as you suggested and order by ID desc - provided of course, your definition of "last" record is the one with the largest ID


or you don't need Q of Q at all... just grad the ID from the last record:

<cfset firstID = firstQ["ID"][firstQ.recordCount]>
0
 
_agx_Commented:
Right, but remember you have to sort first . Otherwise the terms "first" and "last" don't mean anything :) If I'm understanding correctly, you want to sort the results by the largest ID first, then grab the largest one - correct? If yes, you could use the example gdemaria already provided but add an ORDER BY clause to it:

<cfquery name="topfirstQ" dbtype="query" maxRows="1">
     SELECT ID
     FROM   firstQ
     ORDER BY ID DESC
</cfquery>


That said ... it sounds like you just want the largest ID value from the query. If that's the case, you could simply use MAX:

<cfquery name="getLastID" dbtype="query" maxRows="1">
     SELECT  MAX(ID) AS LargestID
     FROM    firstQ
</cfquery>
<cfoutput>#getLastID.LargestID#</cfoutput>
0
 
_agx_Commented:
or you don't need Q of Q at all... just grad the ID from the last record:
<cfset firstID = firstQ["ID"][firstQ.recordCount]>

Oops our responses collided...

EDIT:Yes, IF their first query is already sorted by ID DESC, then that's correct. A QoQ isn't needed.  (That wasn't clear from the original post). Otherwise, they do need some kind of QoQ to resort the results first - so they get the correct record, not an arbitrary value.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now