Let's assume a users
table like:
age | popularity
------------------
16 | 2
23 | 5
17 | 2
16 | 3
... | ...
... | ...
I want to select rows with smaller age and bigger popularity. But not to choose the one over the other.
With:
SELECT *
FROM `users`
ORDER BY `age` ASC, `popularity` DESC
We will have the younger users on top and the users with the same age
would be ordered by popularity among themselves.
And with:
SELECT *
FROM `users`
ORDER BY `popularity` DESC, `age` ASC
We will have the most popular users on top and the users with the same popularity
ordered by age
among themselves.
But I do not want to choose the one sorting over the other. I want to mix them up.
How do I achieve sorting with 60% based on popularity
and 40% based on age
?
Is there a general-purpose solution for such sorting? Is a custom function the only option?
UPDATE: Sample desired results
age | popularity
------------------
16 | 8
15 | 2
23 | 5
29 | 10
16 | 3
16 | 2
17 | 2
So if a user is very young she should be higher even with smaller popularity. If a user is quite popular she should be higher even if she is older.
The most young and most popular should be always on top. The most old and the least popular should be always on the bottom.
I am looking for a general-purpose solution, not just for numbers, but for dates, strings and so on.
select *, priority*0.6+age*0.4 as sortkey from ... order by sortkey.