[RESOLVED] order by total amount of rows in another query.

[jamesm87]

Hey,

I will quicky give you an example of the current DB structure..

Table Rooms:

room_id
room_name

Table Online:
room_id
user_id

When a user becomes online it inserts into the Online table, when they leave they get deleted from the Online table. Is it possible to do a query such as..

select * from rooms order by sum(select * from Online where room_id=room_id) limit 30

Or is it just best to add an additional field to rooms called users_online?

[tsinka]

Hi,

you can do something like:

SELECT r.*, COUNT(o.room_id) AS cnt FROM Rooms r LEFT JOIN Online o
ON r.room_id = o.room_id
GROUP BY r.room_id
ORDER BY cnt DESC

That will order the list descending (remove the DESC if you want to have an ascending order)

cnt will contain the number of online visitors in the given room.

Thomas

[jamesm87]

Thanks!

Works a treat. 🙂

[jheitz]

Hi James,

I'm interested in your approach. I think you might have problems with users that close the browser (or this crashes, if we are talking about IE, quite a common event 😉)... How will you then remove them from your USERS_ONLINE table? Could this be a problem? If it could, maybe you should make sure you check with certain frequency if users are still online...