Min/Max of Select Top X 1

[smdemo]

Is there any way to return the min and max of a field based on the Select Top X in one query? I am getting Error 3122 when I attempt to add an expression to find the min/max and can't figure a way around it other than to create a second query to find a Min/Max value from there. I'm trying to avoid having a second query.

--This is the error I am getting--

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)

Thanks

 

[dhookom]

You could possibly use a subquery in the criteria. For instance, if you want to return the Min and Max Freight from the TOP 10 ORDERS (by Freight DESC) in the Northwind MDB.

SELECT Min(Orders.Freight) AS MinOfFreight, Max(Orders.Freight) AS MaxOfFreight
FROM Orders
WHERE (((Orders.OrderID) In (SELECT TOP 10 OrderID FROM Orders O ORDER BY Freight DESC)));

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

specifically, that error indicates that there's a field in your SELECT clause that's not in the GROUP BY clause.

If you have this

SELECT CustomerID, Count(*) As NumberOfOrder FROM tblOrders GROUP BY CustomerID

if you decide that you want to break it down by state as well then you would have to add STATE to BOTH the SELECT and the GROUP BY portions of the query:

SELECT State, CustomerID, Count(*) As NumberOfOrder FROM tblOrders GROUP BY State, CustomerID

So, any field in your SELECT that isn't an aggregate (SUM, COUNT, MIN, MAX, etc) has to be listed in the GROUP BY.

HTH

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[smdemo]

Thank you Duane, I mistakenly had an = where I needed the "In". Works perfectly now.

Thanks again.