MySQL sort differently in "batch"

Question

Below is the standard query that sort weight in descending order.

SELECT * FROM article ORDER BY weight DESC LIMIT 0, 4
+-------------+--------+
| title       | weight |
+-------------+--------+
| B           | 2      | 
| E           | 2      | 
| Y           | 2      | 
| A           | 1      | 
| C           | 1      | 
| D           | 1      | 
| F           | 1      | 
| G           | 1      | 
| X           | 1      | 
| Z           | 1      | 
| I           | 1      | 
| G           | 1      | 
+-------------+--------+

However, I wish to sort it differently as following based on the weight value.

+-------------+--------+
| title       | weight |
+-------------+--------+
| B           | 2      | 
| A           | 1      | 
| C           | 1      | 
| D           | 1      | 
| E           | 2      | 
| F           | 1      | 
| G           | 1      | 
| X           | 1      | 
| Y           | 2      | 
| Z           | 1      | 
| I           | 1      | 
| G           | 1      | 
+-------------+--------+

The record with weight value 2 only selected once and sorted at the top. Then followed by records with weight value 1.

Try to rephrase the question. For now there is no clear explanation what you want to sort and how. — Marek Musielak, Jul 04 '11 at 14:15
You are asking for an answer that describes an operation (a sort) without ambiguity. You should provide information of the same quality standard. — Jon, Jul 04 '11 at 14:15
please show the entire table structure. How is decided to be B,A,C,D,E the correct order, and not B,F,G,X,E ? — Maxim Krizhanovsky, Jul 04 '11 at 14:48
Your second table makes no sense. There is no discernable pattern that could be used to create a sort clause - if you were sorting by weight, then the two `G` titles would be together. — Marc B, Jul 04 '11 at 14:58

score 1 · Answer 1 · edited May 23 '17 at 11:48

Using the approach like in these answers:

you could obtain row numbers, separately, for rows with weight value of 2 and for rows with any other weight value, then use the resulting numbers for sorting.

Before I continue, please note that, even though the official documentation admits that

You might get the results you expect,

it also advises that,

As a general rule, you should never assign a value to a user variable and read the value within the same statement.

(By the same statement it means a statement other than SET.)

Below is a way of getting the expected order if the results are as they are expected.

SET @row2 = -1;
SET @row_other = -1;

SELECT
  title, weight
FROM (
  SELECT
    title, weight,
    @row2 := @row2 + CASE weight WHEN 2 THEN 1 ELSE 0 END AS weight2_row,
    @row_other := @row_other + CASE weight WHEN 2 THEN 0 ELSE 1 END AS other_weight_row
  FROM article
) s
ORDER BY
  CASE weight WHEN 2 THEN @row2 ELSE @row_other DIV 3 END,
  weight = 2 DESC

The specific order for non-Weight=2 rows is undefined, just like it is in your question.

ypercubeᵀᴹ · Answer 2 · 2011-07-06T08:45:43.587

1

Taking the same precautions that Andriy states, you can also use this:

SELECT title, weight
FROM
    ( SELECT title, weight
           , @rownumber2 := @rownumber2 + 3 AS rn
      FROM article
         , ( SELECT @rownumber2 := 1 ) AS dummy
      WHERE weight = 2
      ORDER BY title                     --- optional, configure it for the 
                                         --- ordering of rows with weight = 2
    UNION ALL

      SELECT title, weight
           , @rownumber1 := @rownumber1 + 1 AS rn
      FROM article
         , ( SELECT @rownumber1 := 3 ) AS dummy
      WHERE weight = 1
      ORDER BY title DESC                --- optional, configure it for the 
                                         --- ordering of rows with weight = 1
    ) AS insaneOrdering

ORDER BY rn
       , weight DESC ;

edited Jul 06 '11 at 08:45

answered Jul 05 '11 at 09:24

ypercubeᵀᴹ

113,259
19
174
235

`(SELECT @rownumber2 := 0)` and then `… @rownumber2 := @rownumber2 + 4`, and for @rownumber1: `(SELECT @rownumber1 := -1)`, `… @rownumber1 := @rownumber1 + 1 + ((@rownumber + 4) % 4 = 3)`, if you meant assigning something like `0, 4, 8 …` to @rownumber2 and everything else (`1, 2, 3, 5, 6, 7, …`) to @rownumber1. But maybe you are implementing something different here. – Andriy M Jul 05 '11 at 15:34
@Andriy: I'm trying `rn=4,7,10,...` with `weight=2` and `rn=4,5,6,7,...` with `weight=1`. I'm sure there are many ways to achieve same result. – ypercubeᵀᴹ Jul 05 '11 at 15:40

MySQL sort differently in "batch"

2 Answers2