MySQL order by field in certain order

Question

I am trying to run an SQL Query in PHP and order by 1 column but in a certain order

i have tried this:

$sql3="SELECT * from extension
where client_id = '".$result2["id"]."' 
ORDER BY FIELD(type, 'term', 'queue', 'ivr', 'voicecentre', 'conference', 'callback', 'intercom', 'queuecentre') ";

but its just not ordering at all

[Seems to work well with VARCHAR](http://sqlfiddle.com/#!2/52d4b/1). What is the type of the field `type` in your schema? — Joachim Isaksson, Feb 21 '14 at 11:28
What is the collation of the table? That can affect how column values are compared with literal strings. — Barmar, May 22 '14 at 23:43

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

Try this. Hope use full for you.

select a.col as type,coalesce (COUNT,0) as count from
(select 'A' as col union all
select 'B' as col union all
select 'C' as col union all
select 'D' as col )a
left join Table1 T
on a.col=T.type
order by FIELD(a.col,'A','B','C','D') ;

Order by FIELD in MYSQL

SQL DEMO LINK

edited May 23 '17 at 11:57

Community

1
1

answered Aug 04 '14 at 20:20

Adeel Gill

353
4
19

echo_Me · Answer 2 · 2014-02-21T12:23:41.397

0

try this

  ORDER BY  case 
              when type= 'term'         then 0
              when type=  'queue'       then 1
              when type=  'ivr'         then 2
              when type=  'voicecentre' then 3
              when type=  'conference'  then 4
              when type=  'callback'    then 5
              when type=  'intercom'    then 6
              when type=  'queuecentre' then 7
          else 8
        end ASC

EDIT:

i guess you have to change the quotes here

 where client_id = '".$result2['id']."'

LOOK DEMO FOR MY AND YOUR QUERY

EDIT2:

But if you are using gotchas sql then this function (ORDER BY FIELD) will not order properly .

look this article here and to fix it.

edited Feb 21 '14 at 12:23

answered Feb 21 '14 at 10:24

echo_Me

37,078
5
58
78

More standard SQL, but any particular reason this should work better than the original? – Joachim Isaksson Feb 21 '14 at 12:00
not tested which one works better, but i gave him other option. – echo_Me Feb 21 '14 at 12:06
@JoachimIsaksson i guess they work same , they took 1ms for both look demo. – echo_Me Feb 21 '14 at 12:15
@JoachimIsaksson men fun du bor nära mig :) – echo_Me Feb 21 '14 at 12:17
this doesn't order as specified. its just listing them as normal – Feb 21 '14 at 12:31
you seen my demo ? it works perfect , its something wrong with your code. please post your sample data in fiddle or in your question – echo_Me Feb 21 '14 at 12:35
that article on the gotcha is great @echo_Me! in this case, you could ensure at least one matches by doing a restriction in the where to "WHERE type IN ('term', 'queue', ...)" etc so it's guaranteed to match at least one... – Jon Marnock Jul 09 '14 at 05:34

score 0 · Answer 3 · answered Jul 08 '14 at 04:59

0

select term,queue,ivr,voicecentre,conference,callback,intercom,queuecentre from extension where client_id = '".$result2["id"]."'

please try this give the name of the field in the order you want to select

answered Jul 08 '14 at 04:59

Vysakh

93
1
11

score 0 · Answer 4 · answered Aug 01 '14 at 16:35

0

$sql3=mysql("SELECT * FROM extension WHERE client_id = '".$result2["id"]."' ORDER BY (type, 'term', 'queue', 'ivr', 'voicecentre', 'conference', 'callback', 'intercom', 'queuecentre')");

answered Aug 01 '14 at 16:35

Douglas David

31
2

score 0 · Answer 5 · answered Aug 03 '14 at 18:11

0

Try and make sure the variable type is set to one of the values in the provided fieldlist.

FIELD(type, 'term', 'queue', 'ivr', 'voicecentre', 'conference', 'callback', 'intercom', 'queuecentre')

answered Aug 03 '14 at 18:11

Erik Ros

1
5

score 0 · Answer 6 · answered Aug 14 '14 at 17:31

0

The best way to do this is to use find_in_set

ORDER BY FIND_IN_SET(type, 'term,queue,ivr,voicecentre,conference,callback,intercom,queuecentre')

answered Aug 14 '14 at 17:31

Ludo - Off the record

5,153
4
31
23

score 0 · Answer 7 · edited Sep 24 '14 at 12:19

0

$sql3 = "SELECT * FROM extension
WHERE client_id = '".$result2["id"]."' 
ORDER BY ASC`";

this works for ordering the items with the column name you used in table.

edited Sep 24 '14 at 12:19

AbcAeffchen

14,400
15
47
66

answered Sep 24 '14 at 11:36

Vinod Ratnakar

29
6

score -1 · Answer 8 · answered Aug 05 '14 at 14:47

-1

SELECT *
FROM Extension,Types
WHERE Extension.typeID = Types.typeID
    AND client_id = '".$result2["id"]."' 
ORDER BY Types.order;

Table Types:

[typeID] => PK
[type]
[order]

Table Extension:

[extensionID] => PK
[typeID]
...

answered Aug 05 '14 at 14:47

Dirk En Carol Rennen

33
5

MySQL order by field in certain order

8 Answers8

SQL DEMO LINK

Table Types:

Table Extension: