[SOLVED] Order results by one field, then by another.

[pocobueno1388]

I'm creating an online users script. What I'm trying to do is display moderators at the top of the list, and then all the others members after them, which I have working fine. The problem is that it orders the moderators from highest ID to lowest. I would like it to go lowest to highest.

 

I have a users table, and a moderators table. I don't think I need to post the structure to the tables as it should be clear when you see the query.

 

SELECT
   u.userID, u.username, m.userID as modID
FROM 
   users u
LEFT JOIN 
   moderators m
ON 
   m.userID = u.userID
WHERE 
   last_active > (NOW() - INTERVAL 5 MINUTE)
ORDER BY 
   modID DESC, u.userID

 

To get the moderators to appear at the top, I have no choice but to do "ORDER BY modID DESC". If I put ASC instead, they appear at the bottom.

 

I know I could easily do this with two separate queries, but if it's possible with one, that would be better. Any help is greatly appreciated, thanks.

 

[fenway]

That's because modID is going to be null for everyone else, and null sorts differently.... if you use IFNULL() and set it to -1, ASC should be fine.

[pocobueno1388]

Okay, the null values are now changing to -1, but it's still not going in the correct order.

 

Updated query:

SELECT 
   u.userID, u.username, IFNULL(m.userID,-1) as modID
FROM 
   users u
LEFT JOIN 
   moderators m
ON 
   m.userID = u.userID
WHERE 
   last_active > (NOW() - INTERVAL 80 MINUTE)
ORDER BY 
   modID ASC, u.userID

 

This displays the moderators in the correct order, but they are at the bottom of the list. If I put DESC instead of ASC I get the same exact result I started with.

 

Did I follow your directions correctly?

[Barand]

does this do it?

SELECT
   u.userID, u.username, m.userID as modID
FROM 
   users u
LEFT JOIN 
   moderators m
ON 
   m.userID = u.userID
WHERE 
   last_active > (NOW() - INTERVAL 5 MINUTE)
ORDER BY 
   modID IS NULL, u.userID

[pocobueno1388]

Yes, that did it. Thank you both so much.