I currently need to order data by highest value down, and then lowest value up, in between.

My Query is close, but doesn't quite order by largest down, though it is inserting the lowest in between:

DEMO Fiddle

select users.* 
from users CROSS JOIN (select @even := 0, @odd := 0) param
order by 
    IF(score > 1, 2*(@odd := @odd   1), 2*(@even := @even   1)   1),
    score DESC;

Current Results

Email           Score  
-----           --------
[email protected]  42
[email protected]  1
[email protected]  49
[email protected]  0
[email protected]  37
[email protected]  7
[email protected]   22

Desired Results

Email           Score  
-----           --------
[email protected]  49
[email protected]  0
[email protected]  42
[email protected]  1
[email protected]  37
[email protected]  7
[email protected]   22

CodePudding user response：

You can achieve using MySQL but avoid such complex SQL statements as the same can be achieved using programming language very easily.

SET @totalRows := (CASE WHEN (SELECT COUNT(*) FROM users) IS NULL THEN 0 ELSE (SELECT COUNT(*) FROM users) END);
PREPARE stmt1 FROM '(SELECT t.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, email, score FROM users UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY score ASC) AS row_num, email, score FROM users) AS t ORDER BY t.row_num, t.score DESC LIMIT 0,?)';
EXECUTE stmt1 USING @totalRows;

Here is the explanation to achieve it:

1. Set variable @totalRows contains total rows in users table as that many rows will be displayed as a final result set.
2. Used ROW_NUMBER() function of MySQL to set ordering based on SOCRE field DESCENDING and ASCENDING for another result set.
3. Combined both the result set using UNION ALL statement of MySQL
4. Add the LIMIT keyword to make sure the final result must have a total number of rows that should not exceed @totalRows variable.
5. As in MySQL LIMIT statement we can't pass a dynamic value at a query level. I used the approach of prepare a statement.

You can ignore row_num column I used as final result set.

Hope my solution will help you.

CodePudding user response：

For MySql 8.0 you can use ROW_NUMBER() window function:

SELECT email, score
FROM (
  SELECT *,
         ROW_NUMBER() OVER (ORDER BY score DESC) rn1,
         ROW_NUMBER() OVER (ORDER BY score ASC) rn2
  FROM users 
) t
ORDER BY LEAST(rn1, rn2), rn1;

For previous versions you can simulate ROW_NUMBER() with correlated subqueries (with the cost of poor performance for large datasets):

SELECT email, score
FROM (
  SELECT u1.*,
         (SELECT COUNT(*) FROM users u2 WHERE u2.score > u1.score) rn1,
         (SELECT COUNT(*) FROM users u2 WHERE u2.score < u1.score) rn2
  FROM users u1 
) t
ORDER BY LEAST(rn1, rn2), rn1;

See the demo.