CODEWITHSUNDEEP
mysqlsqlsql-order-by
valek
valek

Reputation: 1376

Order data by data from another table

I have two tables:

rooms (all the rooms)

id | title  | ...
-----------------
1  |Room 1  |
2  |Room 2  |
3  |Room 3  |

user_rooms (in which room is every user, column user is user's id and it's primary column)

user  | room | ...
------------------
20    | 3    |
14    | 1    |
35    | 3    |

So I want to select all the rooms from the 'rooms' table but to order them in that way to show the rooms with the most users in them and after that the rooms with less and less users. For example, I want to show room 3 first (because 2 users are in it), then room 1 (one user in it), and finally room 2 (since no users are in it). How to achieve that?

Upvotes: 0

Views: 51

Answers (5)

marz
marz

Reputation: 9

Please try the following query:

select * from rooms r order by (select count(1)
from userroom ur where ur.roomid = r.roomid ) desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

This would often be done without a subquery:

select r.id, r.title, count(ur.room) as numusers
from rooms r left join
     user_rooms ur
     on r.id = ur.room
group by r.id, r.title
order by numusers desc;

This would often be more efficient than a version using a subquery in the from clause because it can take advantage of an index on the join key.

Interestingly, the same index would be used for a correlated subquery in the select, which is an alternative approach:

select r.id, r.title,
       (select count(*)
        from user_rooms ur
        where r.id = ur.room
       ) as numusers
from rooms r
order by numusers desc;

This might be the most efficient approach, because it removes the aggregation in the outer query.

Upvotes: 2

genespos
genespos

Reputation: 3311

This will give you only rooms with users 

    SELECT title, count(user) As MostUsers
    FROM Rooms R
    INNER JOIN user_rooms U
    ON R.?field? = U.?field?
    GROUP BY title
    ORDER BY MostUsers

You need to complete the query inserting the names of the fields that you can use to Join the tables

If you want all rooms you can use a Left Join:

    SELECT title, count(user) As MostUsers
    FROM Rooms R
    LEFT JOIN user_rooms U
    ON R.?field? = U.?field?
    GROUP BY title
    ORDER BY MostUsers

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select r.id, r.title, coalesce(t.cnt,0)
from rooms r left join
(select room, count(*) as cnt
from user_rooms
group by room) t on t.room = r.id
order by t.cnt desc

Upvotes: 1

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

SELECT aa.id, aa.title
FROM rooms AS aa
LEFT JOIN (
    SELECT room, COUNT(*) AS total_count
    FROM user_rooms
    GROUP BY room
) AS _aa
ON aa.id = _aa.room
ORDER BY _aa.total_count;

Upvotes: 2

Related Questions