Sorting by date in MySQL

Question

Sorting by date in MySQL

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

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

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

score - in-game user score.

On my site I have a list where I can list these users, and I am currently ordering as follows:

ORDER BY dh_premium_expires DESC, score DESC;

I want premium users to come first and then the ones with the highest score. That way it's working, but there's a problem, premium users with more premium time are getting to the top and I wanted it to stay that way:

Premier users with the highest score. Primitive users. Users with higher score.

For this I would have to create a variable setting 1 for who is premium (dh_premium_expires> today's date) and 0 for who is not or has already expired. That would be correct I think.

How do I do this in MySQL?

mysql sql

asked by anonymous 01.04.2014 / 13:17

2 answers

1

Have you tried

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

01.04.2014 / 13:28

What jQuery plugin lets you select days and events each day in a horizontal timeline? Multiple Bank Login Control

score 2 · Accepted Answer

No need to create another field, this 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 record is premium or not by entering the result of your query 0 or 1. All those with "1" (premium account) appear first and then we sort by the score.