MySQL ORDER BY CASE / IF relevance issue

Question

I've never worked with ORDER BY CASE or ORDER BY IF() and the few examples I've found on the internet are more confusing than helping me trying to accomplish following task:

I have a member list and I want to "reward" the user's activity a bit by ordering a user higher (to the top) in this member list.

In my example, I have a MySQL table called "users" with 3 columns:

  user_percentage     |     user_photo     |     user_lastlogin
    ----------------------------------------------------------
        12                      1                  1356389646
        42                      1                  1456549641
        37                      0                  1776389443
        84                      1                  1356535535
        56                      0                  1868689646
        66                      0                  1811189622
        71                      1                  1656779645

"user_percentage" holds the value (0 - 100) of all filled in profile fields by each user.
"user_photo" holds the value (0 = false , 1 = true) if a user has upload a profile photo.
"user_lastlogin holds the value (timestamp) of their last visit.

It's a bit difficult to explain, but to say it simple what I want:

A user with a higher user_percentage value should be on top of the member list, but if he got no user_photo, then he should be "moved down" in the member list, same if he got an old user_lastlogin timestamp, then list him even more down in the member order.
Also what I'm trying to prevent is that a user has signed up, filled in all profile fields (then user_percentage value will be = 100) and uploaded a photo (then user_photo will be = 1), but hasn't login anymore since a long time (so he has a very old user_lastlogin = timestamp), then I want this user moved down in the member list order.

My question is: Is this somehow possible to do with 1 MySQL ORDER BY statement?

Let's say user_photo has just an importance of 30% while user_lastlogin got a higher importance and user_percentage also a bit higher.

Any ideas?

Best regards!

score 0 · Answer 1 · answered Dec 25 '12 at 01:58

0

In order to even begin writing the SQL, you need to clarify how much each parameter is worth.

You should produce a list of examples and then convert it into either a simple calculation like:

(photo*timesincelogin*20)+(percentage)

or CASE based statements which each have a calculation - i.e:

ALL people with photos THEN ALL people with out, subsorted by time*percentage.

Hand-wavy 'move down a bit' is not specific enough for an answer with SQL commands.

answered Dec 25 '12 at 01:58

Philip Whitehouse

4,293
3
23
36

Of course, 'moving a bit down' was meant to be for the case a user has 100 user_percentage + user_photo = 1, but a very old user_lastlogin date, then because of this old _lastlogin date he should be listed under a user with a newer _lastlogin date. – Dec 25 '12 at 15:07
But how much newer? Is 1 year enough? 1 month? 1 day? 1 second? – Philip Whitehouse Dec 25 '12 at 18:55
Yes, let's make it simple, after 1 year not logged in (user_lastlogin) they becomes old. Also, if no user_photo, they should be listed at the end of the list. – Dec 26 '12 at 22:00

MySQL ORDER BY CASE / IF relevance issue

1 Answers1