如何在顶级和离线后显示在线用户呢？

Question

Could you guys please tell me how to show all online people on the top and all offline after, both lists in ascending order of the username just like Skype does.

My MySQL table is Users, and the columns are as follow:

ID
Username
Last_logged_in - is with unix_timestamp();

the code I am using to show online people is as follow:

select * from users where Last_logged_in >= UNIX_TIMESTAMP() - 60;

and the code I tried was like so:

select id, username,  last_logged_in from users where last_logged_in >= UNIX_TIMESTAMP() - 60 UNION select id, username,  last_logged_in from users where  last_logged_in <= UNIX_TIMESTAMP() - 60 LIMIT 10;

some helps will be really appreciated.

Answer 1

Your query doesn't provide the results you expect because UNION does not preserve the order of the rows from the queries it unions.

You need to enclose each of the unioned queries into parentheses and add ORDER BY after the last query. However, if you do that you obtain a big and slow query that selects all the records from table users in a convoluted way.

The solution is to order the rows descending by column Last_logged_in. This way, the users that logged in recently are returned first. Also you need to add in the fields list the expression that tells if the user is still online. Finally, because your query does not filter the returned users, a LIMIT clause is recommended; otherwise the query will return the entire table which is probably not what you want.

Update

As the OP specified in a comment, the users must be sorted by their online status (online first) and then by their usernames (ascending).

The (updated) query is:

SELECT u.*, IF(Last_logged_in >= UNIX_TIMESTAMP() - 60, 1, 0) as isOnline;
FROM users u
ORDER BY isOnline DESC, username ASC
LIMIT 20