Sorting result with absolute placement

Question

I have a table of ids and places, is it possible to get rows set at specified places?

To get

ID  PLACE
5   (null)
3   2
4   (null)
6   4
2   (null)
1   (null)

instead of

ID  PLACE
6   4
5   (null)
4   (null)
3   2
2   (null)
1   (null)

For this data it means to have a row with place = 2 as the second row in result and row with place = 4 as the fourth in result, while other rows cover them in their original order.

Answer

select * from records 
order by FIELD(ID, 5,3,4,6,2,1)

is nice and funny, but wrong.

What is "their original order" - why does ID 5 come first? (Rows in a relational database do not have any order - if you want a specific order in your query output then you need to specify that with an order by clause.) — Lord Peter, Apr 18 '13 at 07:49

score 2 · Accepted Answer · edited May 23 '17 at 12:28

SELECT CASE WHEN NULLID IS NULL 
       THEN ID
       ELSE NULLID
       END AS ID, 
       toFillTable.place
FROM 
(
    SELECT CASE WHEN id IS NULL 
           THEN @placeHolder := @placeHolder + 1 
           ELSE null 
           END as placeHolder
         , r.*
    FROM
    (
        SELECT @row := @row + 1 as row
        FROM records t1, (SELECT @row := 0) t2
    ) s
        INNER JOIN (SELECT @placeHolder := 0) t12
        LEFT OUTER JOIN records r
            ON s.row = r.place
    order by row
) toFillTable
    LEFT OUTER JOIN
    (
          SELECT @row1 := @row1 + 1 as nullRowPlace, t1.id as nullId
          FROM records t1, (SELECT @row1 := 0) t2
          where place is null
          order by t1.id desc
    ) nullPlaceTable ON toFillTable.placeHolder = nullPlaceTable.nullRowPlace

The first query output result :

toFillTable

placeHolder    Id    Place
    1        (null)  (null)
  (null)       3       2
    2        (null)  (null)
  (null)       6       4
    3        (null)  (null)
    4        (null)  (null)

It gives the Id when place is given on the right row, and give a counter named PlaceHolder on the row where place is null.

The second query:

nullPlaceTable 

NullPlaceholder    IDNull
     1                5
     2                4
     3                2
     4                1

It gives the rank of the row when place is null.

The join of the two gives what is expected!

Fiddle.

Reference:

generate an integer sequence in MySQL

Awesome! Thank you very much for your efforts. – vearutop Apr 18 '13 at 08:25 — vearutop, Apr 18 '13 at 08:25

Sorting result with absolute placement

1 Answers1