mukidi mukidi - 2 months ago 13
MySQL Question

mysql: order a column in specific order

I need to order my current table into specific order.

This is my table:

+----------+----------+----------+
| data_src | data_pid | data_rnd |
+----------+----------+----------+
| north | 1 | e8fefa9 |
| north | 2 | ldfkeo3 |
| north | 3 | iwe7361 |
| south | 11 | mdk282k |
| south | 12 | 9eoirkr |
| south | 13 | qpeiek0 |
| west | 71 | ndbhee2 |
| west | 82 | 8eujeuq |
| west | 95 | lkkhjut |
| east | 22 | 87u4y3w |
| east | 23 | 0394yrw |
| east | 24 | opeolei |
+----------+----------+----------+


and i want to order it into something like this:

+----------+----------+----------+
| data_src | data_pid | data_rnd |
+----------+----------+----------+
| east | 22 | 87u4y3w |
| north | 1 | e8fefa9 |
| south | 11 | mdk282k |
| west | 71 | ndbhee2 |
| east | 23 | 0394yrw |
| north | 2 | ldfkeo3 |
| south | 12 | 9eoirkr |
| west | 82 | 8eujeuq |
| east | 24 | opeolei |
| north | 3 | iwe7361 |
| south | 13 | qpeiek0 |
| west | 95 | lkkhjut |
+----------+----------+----------+


How can i achieve that?

thanks

Answer

E.g.:

SELECT x.* 
  FROM my_table x 
  JOIN my_table y 
    ON y.data_src = x.data_src 
   AND y.data_pid <= x.data_pid 
 GROUP 
    BY x.data_src
     , x.data_pid 
 ORDER 
    BY COUNT(*),data_src;

or, faster...

SELECT data_src
     , data_pid
     , data_rnd 
  FROM 
     ( SELECT *
            , CASE WHEN @prev = data_src THEN @i:=@i+1 ELSE @i:=1 END rank
            , @prev:=data_src 
         FROM my_table
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY data_src
            , data_pid
     ) x 
 ORDER 
    BY rank
     , data_src;