选择所有项目，按使用项目的次数排序

Question

This query makes sense in my head but i've spent ages trying to write it and not getting any where. I'm using Codeigniter and it's Active record function, but any standard MYSQL would do.

I have a list of venues, and a list of events. A venue can have a number of events at it, and each events stores which venue it is at.

I want to run a query that fetches all venues, and orders them by the number of events they have.

So the first in the list will be the venue with 20 events, the last the venues with 0 events.

Can anybody help with the query? Table structure is simply;

Venues(id, name, address)
Events(id, venue_id, event_name)

Any help would be greatly appreciated.

Thanks

Answer 1

something like this?:

SELECT v.id, COUNT(*)
FROM Venues v, Events e
WHERE v.id = e.id
GROUP BY v.id
ORDER BY COUNT(*) DESC;