CODEWITHSUNDEEP
mysql
Nitish Vasu
Nitish Vasu

Reputation: 49

How to define a custom ORDER BY in MySQL query

I need output in following order(firstly, group by last 3 letters and then arrange in order based on the first 3 digits)

ColumnA

001_eng
004_eng
002_chn
003_usa

But order by ColumnA gives me

ColumnA

001_eng
002_chn
003_usa
004_eng

This is just sample data. I have hundreds of entries in this format and the values keep changing everyday. So, specifying all the entries inside the field is not a feasible option. I'm not sure of how to use FIELD() in my case.

Upvotes: 0

Views: 3011

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

You can use SUBSTRING_INDEX in case all ColumnA values are formatted like in the sample data:

SELECT *
FROM mytable
ORDER BY FIELD(SUBSTRING_INDEX(ColumnA, '_', -1), 'eng', 'chn', 'usa'),
         SUBSTRING_INDEX(ColumnA, '_', 1)

Demo here

Upvotes: 0

fthiella
fthiella

Reputation: 49089

You can use FIELD:

select *
from tablename
order by
  FIELD(ColumnA, '001_eng', '004_eng', '002_chn', '003_usa')

(please be careful if ColumnA is not in the list the field function will return 0 and the rows will be put on top)

or you can use CASE WHEN:

select *
from tablename
order by
  case
    when ColumnA='001_eng' then 1
    when ColumnA='004_eng' then 2
    when ColumnA='002_chn' then 3
    when ColumnA='003_usa' then 4
    else 5
  end

or you can use a different languages table where you specify the order:

id | name    | sortorder
1  | 001_eng | 1
2  | 002_chn | 3
3  | 003_usa | 4
4  | 004_eng | 2

then you can use a join

select t.*
from
  tablename t inner join languages l
  on t.lang_id = l.id
order by
  l.sortorder

(with proper indexes this would be the better solution with optimal performances)

Upvotes: 2

Vipin Jain
Vipin Jain

Reputation: 3756

you can use substring() and get order by 

SELECT * 
FROM table_name 
ORDER BY SUBSTRING(ColumnA, -7, 3);

Upvotes: 0

Related Questions