ACCESS SQL: TOP 1 of a subcategory (or MAX())

If the question doesn't seem very clear, it should be after this explanation !

Here is my table: Flights (AC, hours, cycles, flight_date)

ac = aicraft开发者_开发问答

hours = total aircraft hours

cycles = total aircraft cycles (cycle = flight) flight_date = self explanatory :)

I'm trying to write a query that will give me the number of hours/cycles all AC have reached on a specific date.

(Keep in mind aircrafts can make a few flights a day, or no flight at all)

Here is what I wrote:

SELECT ac, hours, max(cycles)
FROM flights
WHERE flight_date <= cdate(asked_date)  //so access asks me for the date when I run the query
GROUP BY ac, hours

but for some reason, this doesn't work:

It returns all the flights from all the aircraft where flight_date <= asked_date (looks like the MAX isn't taken into account at all).

I considered using TOP 1 with a subquery but couldn't find how to do it.

I'm using max(cycles) since if the cycle length is < 1h, we can get records with the same hours, but the cycles will still be different by definition.

What am I missing ?

---

You need to put max also around the hours column and group only by ac:

SELECT ac, max(hours), max(cycles)
FROM flights
WHERE flight_date <= cdate(asked_date)
GROUP BY ac

Reason:
As the hours column contains the total hours of the aircraft, grouping by this value, will return you (nearly) all rows, because the value is different in each row. It will only group this rows that have the same value of total hours for a specific aircraft.

---

Remove the hours from both select and group by:

SELECT ac, max(cycles) 
FROM flights 
WHERE flight_date <= cdate(asked_date)   
GROUP BY ac

if you really need the matching hours of the cycle then:

SELECT x.ac, x.cycles, hours
FROM flights 
inner join 
(SELECT ac, max(cycles) as cycles
FROM flights 
WHERE flight_date <= cdate(asked_date)  
GROUP BY ac) x 
on x.ac=flights.ac and x.cycles=flights.cycles