Sort by date in Mysql

Question

I have a table with two fields: dh_premium_expires and score.

dh_premium_expires - the date/time the user premium will expire.

score - score of the user within the game.

On my site I have a list where I list these users, and currently I am ordering in the following way:

ORDER BY dh_premium_expires DESC, score DESC;

I want the Premiums users to come first and then the highest score users. This way is working, however has a problem, premium users with more premium time are getting on top and I wanted it to be in that order:

Users with higher score awards.
Premium users.
Users with higher score.

For this I would have to create a setando variable 1 for those who are premium (dh_premium_expires > today’s date) and 0 for those who are not or have already expired. That would be right I guess.

How do I do this in Mysql?

Answer 1

There is no need to create another field, it will only "pollute" your database. See, it can be done as follows:

SELECT 
    IF(dh_premium_expires<now(),0,1) as premium, dh_premium_expires, score 
FROM 
    tabela 
ORDE BY 
    premium DESC, score DESC

The above query checks whether the registration is premium or not by inserting in the result of your query 0 or 1. All that have "1" (premium account) appear first and then sort by score.

Answer 2

you’ve tried to

select 
dh_premium_expires , score, case when (dh_premium_expires > DATA_HORA) then 1 else 0 end as ISPremium
from
  TABELA