SQL... Get Value from Field based on Max value of Another Field

Having a complete SQL mental lapse today and can't figure this out. ColdFusion an开发者_如何学JAVAd MS Access

I'm looking to do a Query of Query in ColdFusion.

Original query:

<cfquery name="myQuery" datasource="xxx">
SELECT Name, ID, tblLoc.Directions, tblLoc.LocationOrder
FROM myTable 
WHERE .....             
ORDER BY tblLoc.LocationOrder
</cfquery>

I want to create another query from this query where I retrieve Directions where LocationOrder is max.

Thus, if myQuery returned:

Name       ID        Directions      LocationOrder
AA         10         AAAAAA            1
BB         11         BBBBBB            2

My QoQ would return:

Directions
BBBBBB

But every iteration of a seemingly simple SQL I've tried fails. I must be half-asleep or something.

<cfquery name="latestDirections" dbtype="query">
SELECT Directions
FROM myQuery
WHERE LocationOrder=(select max(LocationOrder) from myQuery)
</cfquery>

<cfquery name="latestDirections" dbtype="query">
SELECT Directions, MAX(LocationOrder) as maxLocationOrder
FROM get_sel_locations
</cfquery>

nope... neither will do it.

...The second, "Column get_sel_locations.Directions is invalid in the SELECT list clause because it is not contained in an aggregate function and there is no GROUP BY clause"

The error is pretty clear. You are missing the GROUP BY clause. When using aggregates like MAX() you have to GROUP the results by any non-aggregated columns. In this case: Directions

SELECT Directions, MAX(LocationOrder) as maxLocationOrder
FROM get_sel_locations
GROUP BY Directions

Update But if just you want the maximum for everything, then Yisroel's query is probably what you want.

Update As mentioned in the comments, QoQ are not database queries. They only support the functions/syntax listed in the documentation http://livedocs.adobe.com/coldfusion/8/using_recordsets_3.html

SELECT Name, ID, tblLoc.Directions, tblLoc.LocationOrder 
FROM myTable  
WHERE .....              
ORDER BY tblLoc.LocationOrder DESC LIMIT 1;

should do you. Your Directions at max LocationOrder is taken care of by sorting and limiting.

Another possibility, if I understand the question correctly:

SELECT x FROM table_name WHERE y = (SELECT MAX(y) FROM table_name)

What happens if you eliminate the ORDER BY clause from myQuery? Also, say how the query fails. What results are you getting?

Given the error you're getting, you might try this:

-- remove the parentheses
WHERE LocationOrder=select max(LocationOrder) from myQuery           

or

-- use IN instead of equals even though your subquery returns a single scalar value and '=' should be fine.
WHERE LocationOrder  IN (select max(LocationOrder) from myQuery)

You can first do a QoQ to get the max location

<cfquery name="maxDirections" dbtype="query">
  SELECT max(LocationOrder) as maxLocation 
  FROM myQuery
</cfquery>

<cfquery name="latestDirections" dbtype="query">
  SELECT Directions
  FROM myQuery
  WHERE LocationOrder = '#maxDirections.maxLocation#'
</cfquery>