MySql ORDER BY multiple columns IF AND

Question

So I've joined two tables for a particular query. After a complicated SELECT statemnt, the table would look vaguely like this: $myid = 3

PostID | FromUserID | To User ID|Date     | PostSeen | NumLikes  |SeenLike
61     |     48     |     3     | 5/11/13 |    1     |     1     |  1
59     |     3      |     3     | 4/11/13 |    1     |     1     |  0
58     |     3      |     3     | 4/11/13 |    1     |     1     |  1
25     |     47     |     3     | 3/11/13 |    1     |     2     |  0
53     |     56     |     3     | 2/11/13 |    0     |     3     |  1
21     |     3      |     55    | 1/11/13 |    1     |     0     |  null
20     |     56     |     3     | 30/10/13|    1     |     0     |  null
18     |     47     |     3     | 29/10/13|    0     |     0     |  null

If NumLikes is equal to 0 then the SeenLike column is null. The PostSeen will always be 0 or 1. Ive put the date in a simplified format here, but its proper DateTimed.

Essentially, I want do something that is the equivalent of

if(FromUserID == $myid && NumLikes > 0){
ORDER BY SeenLike ASC, Date DESC
}
if (FromUserID != $myid) {
ORDER BY PostSeen ASC, Date DESC
}

(I know this is totally wrong, its just to illustrate vaguely what I'm aiming for) So that it would order the results something like the following:

PostID | FromUserID | To User ID|Date     | PostSeen | NumLikes  |SeenLike
59     |     3      |     3     | 4/11/13 |    1     |     1     |  0
25     |     47     |     3     | 3/11/13 |    1     |     2     |  0
53     |     56     |     3     | 2/11/13 |    0     |     3     |  1
18     |     47     |     3     | 29/10/13|    0     |     0     |  null
61     |     48     |     3     | 5/11/13 |    1     |     1     |  1
58     |     3      |     3     | 4/11/13 |    1     |     1     |  1
21     |     3      |     55    | 1/11/13 |    1     |     0     |  null
20     |     56     |     3     | 30/10/13|    1     |     0     |  null

How would I go about doing this? I've tried IF's and CASE's but none have worked so far. Would appreciate an explanation to any answer as well! Thanks for any help!

v010dya · Accepted Answer · 2013-11-05 18:19:56Z

2

You could create two subqueries and then UNION them. The first subquery would have 'WHERE FromUserID = $myid AND NumLikes > 0 ORDER BY SeenLike ASC, Date DESC' and the second 'WHERE FromUserID != $myid ORDER BY PostSeen ASC, Date DESC'.

answered Nov 5, 2013 at 18:19

v010dya

5,8867 gold badges31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SamA Over a year ago

I think this may be the only way to accomplish exactly what you want.

foibs · Accepted Answer · 2013-11-05 18:50:38Z

1

You cannot order half the results by one clause and the rest by another clause. If you think about it, it doesn't make any sense.

However, you can use any type of expression that evaluates like a boolean or an integer in the order clause

for example

ORDER BY FromUserID = $myid desc, -- this will be true or false depending on the fromuserid
    (FromUserID = $myid)*SeenLike ASC, --this will be 0 if fromuser is not equal to myid or =seenlike otherwise
    (FromUserID != $myid)*PostSeen ASC, -- and so on
    Date DESC

edited Nov 5, 2013 at 18:50

answered Nov 5, 2013 at 18:45

foibs

3,4141 gold badge21 silver badges13 bronze badges

Collectives™ on Stack Overflow

MySql ORDER BY multiple columns IF AND

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related