MySQL query help please...

[bu22]

I'm having problems returning a 'TOP 10 Car parks' result set: -

I have a 'carparks' table with 2 fields: -

carpark_id (int)
name (varchar)

And a 'bookings' table with 3 fields: -

booking_id (int)
price (float)
carpark_id (int)

I need a query to return the top 10 car parks (based on the most occurences of carpark_id's in the 'bookings' table. In the returned result set, I need the carpark_id and name.

I've been struggling with this trying out group by, order by and limit, but with no success... any ideas?

Thanks,
James

[nemonoman]

Top 10 car parks:

SELECT carparks.name, count(carparks.carpark_id)
FROM carparks, bookings
WHERE carparks.carpark_id=bookings.carpark_id
GROUP bookings.carpark_id
ORDER BY 2 DESC
LIMIT 10

ORDER BY 2 == order by second column

[bu22]

Thanks for your help! 🙂