CODEWITHSUNDEEP
mysqlsql-order-by
voldomazta
voldomazta

Reputation: 1340

MySQL weird ordering requirement

I have a MySQL query like so:

SELECT
id,
CASE
WHEN (cond1) THEN 1
WHEN (cond2) THEN 2
WHEN (cond3) THEN 3
END `rank`
FROM `table`

Is there a way to order it so that the result I get will have an alternating order of:

rank
1
2
3
1
2
3

The order of the 1s, 2s and 3s doesn't matter as long as they follow the pattern 123123[...]. It can be the order they were entered into the database or random.

I have a database which consists of three types of items and they can be repeatable.

rock      1
paper     2
scissors  3
rock      1
rock      1
rock      1
scissors  3
paper     2

What i want to do is order them rock, paper, scissors, rock, papers, scissors for as long as it can reach.

I'm doing this because a majority of my data (> 90%) is scissors and when I'm displaying my data with pagination, the first page (LIMIT 0, N) is all scissors. Even ORDER BY RAND() wouldn't help because of this majority so this is why.

Upvotes: 1

Views: 121

Answers (2)

Nedret Recep
Nedret Recep

Reputation: 728

Modification from a similar question's answer in Stackoverflow:

SELECT name, cond
FROM (
   SELECT name, cond, @a := @a+1 AS inc 
       FROM table, ( select @a := 0 ) as SqlVars 
       WHERE cond = 1
   UNION
   SELECT name, cond, @b := @b+1 AS inc 
       FROM table, ( select @b := 0 ) as SqlVars 
       WHERE cond = 2
   UNION
   SELECT name, cond, @c := @c+1 AS inc 
       FROM table, ( select @c := 0 ) as SqlVars 
       WHERE cond = 3
)
ORDER BY inc

It's limited to finite small number of conditions. If the conditions are not fixed limited small then you should go with procedure. And it is not an optimal query either.

Another approach is to add a new field in the database and take care of inserting a goog value there (MAX of the same condition + 1) so that use that column for ordering.

Upvotes: 2

Gustav Bertram
Gustav Bertram

Reputation: 14901

How about using modulus?

SELECT id, (((id - 1) % 3) + 1 ) FROM [...]

If your id starts at 1, and goes up without interruption, the second column will go 1,2,3,1,2,3.

If you select from a MySQL row generator, and do an inner join on your original table on rank, you can probably make it work this way.

Upvotes: 0

Related Questions