Take the 2-minute tour ×

I have a table with several columns, and four of them are just generic integer values (num1, num2, num3, num4) and the source of data has these values in random order. In php I would fetch the row, assign the four ints to an array, sort the array, assign the sorted values back and save the row. I would like to do this with sql. So, with a table like

id, num1, num2, num3, num4
1    6      2    9     1
2    12     3    8     4


I would like the results to be

id, num1, num2, num3, num4
1     1     2     6     9
2     3     4     8    12


I have Googled everything I can think of and scoured the Mysql ref manual but can not seem to find a way to do this. Any thoughts or should I just write a php script?

share|improve this question
add comment

1 Answer

Well that's an unpivot, a sort and a pivot, but last I heard not in mysql so maybe

Set @position = 0
  Select Id,@position = @position + 1, @position % 4 as Position, Value From
    Select Id,Position,Value From
       select Id, 1 as Position, num1 as Value
       select Id, 2, num2
       select Id, 3, num3
       select Id, 4, num4
    ) depivot 
    Order by Id, Value
Select ID,V1.Value as Num1, V2.Value as Num2, V3.Value as Num3, V4.Value as Num4 
From OrderedUnpivot v1
Inner join OrderedUnpivot v2 ON v1.Id = v2.ID
Inner join OrderedUnpivot v3 ON v1.Id = v3.ID
Inner join OrderedUnpivot v4 ON v1.Id = v4.ID

Messy but food for thought, oh and not tested or even syntax checked.

share|improve this answer
add comment

Your Answer


