CODEWITHSUNDEEP
mysqlsorting
Choo Hwan
Choo Hwan

Reputation: 416

MySQL sort by two columns of one table

I have this table:

CREATE TABLE table_name (id INT, for_id INT, comms VARCHAR(50));
    INSERT INTO table_name VALUES (1,0, 'boo'),
                                    (2,1, 'John'),
                                    (3,0, 'Zoe'),
                                    (4,3, 'bar'),
                                    (5,1, 'Don'),
                                    (6,3, 'FOO'),
                                    (7,1, 'Doe');

http://sqlfiddle.com/#!9/859933/6

How can I get next:

1.0.'boo'
2.1.'John'
5.1.'Don'
7.1.'Doe'
3.0.'Zoe'
4.3.'bar'
6.3.'FOO'

Thanks!

Upvotes: 1

Views: 101

Answers (3)

Blank
Blank

Reputation: 12378

Not sure this is what you exactly want or not, you should do some explanation for the result order in post.

select *
from table_name
order by 
    case when for_id = 0 then concat(id, for_id) + 0
         else concat(for_id, id) + 0
    end

and SQLFiddle Demo Here.

Updated:

Try following again,

select *
from table_name
order by 
    case when for_id = 0 then mod(concat(for_id, id) + 0, 10)
         else mod(concat(id, for_id) + 0, 10)
    end
    , id asc

SQLFiddle

Updated again:

select *
from table_name
order by 
    case when for_id = 0 then id
         else for_id
    end
    , id asc

SQLFiddle

Upvotes: 2

Shivkumar kondi
Shivkumar kondi

Reputation: 6762

Use order by and Cancat

select Concat(id,'.',for_id) as id ,comms
from table_name
order by id asc,for_id asc

Updated :

Use order by and apply case statement as per your condition

select *
from table_name
order by 
    case when for_id = 0 then concat(id, for_id) + 1
         else concat(for_id, id) + 0
    end

http://sqlfiddle.com/#!9/859933/25

Upvotes: 0

Nir Levy
Nir Levy

Reputation: 12953

Simply use order by , it can get more than one column 

Select * from table_name 
  order by id asc, for_id asc;

Upvotes: 1

Related Questions