mysql query order by multiple columns

[shortysbest]

I am loading notifications from a database table called "notifications" and I am having a little trouble getting them to order in the correct way.

 

my query I'm using right now:

 

$query = mysql_query("SELECT B.* FROM (SELECT A.* FROM notifications A WHERE A.user_id='$session' AND A.from_id!='$session' ORDER BY A.id ASC ) AS B ORDER BY B.state ASC LIMIT 7");

 

this works well as far as showing the unread notifications on top, then the read notifications below, however it's not ordering the two sets by ID (which the id auto increments so the higher id number is the newest) from newest on top to the oldest on bottom, still keeping them separated by the unread on top, read on bottom (column name is state for showing whether they're read or not).

The order the notifications are displaying by their ID is:

 

3

5

2

4

1

 

when it should be:

 

5

3

2

4

1

[sasa]

SELECT A.* FROM notifications A WHERE A.user_id='$session' AND A.from_id!='$session' ORDER BY B.state ASC, A.id DESC LIMIT 7

 

[shortysbest]

Thank you, but unfortunately that doesn't work :| It doesn't return any results.

[monkeytooth]

This is a short non-descript answer, but I'm not the best with it either. But look into the "GROUP BY" reference for mySQL unfortunately all in all in the end of the output it comes down to a specific column to ORDER BY, no matter how you spin it (i think)

[sasa]

ups my misteke

use

SELECT A.* FROM notifications A WHERE A.user_id='$session' AND A.from_id!='$session' ORDER BY A.state ASC, A.id DESC LIMIT 7

not '...ORDER BY B.state ASC ...'

[shortysbest]

That seems to work wonderfully, Thanks!