Getting data in a certain order using mySql

Question

I'm busy writing a PHP app that gets data from a database, but I need to get the data in a certain order.

My Query looks as follows

$sql = "select id,title,type from campaigns where id=$cid order by type";

Now my problem is these are the different types 'GC','MJ','MU','MS','MW','MX','GS' and I want MX to always be select last, thus should always be at the end of the row. So the others shpould firstly be selected and then MX. I hope this makes sense.

Answer 1

You can sort like this:

ORDER BY IF (type = 'MX', 1, 0), type

Answer 2

$sql = "select id,title,type from campaigns where id=$cid and type != 'MX' order by type 
union
select id,title,type from campaigns where id=$cid and type = 'MX'"; 

Answer 3

How many rows are you selecting with your query? Looks like only one (I'm supposing that id is unique...) so the sort order has no effect.

Apart from that your sort order should do what it is supposed to do because alphabetically, MX will be the last in the list.

Answer 4

(select id,title,type from campaigns where id=$cid and type <> 'MX' order by type)
UNION
(select id,title,type from campaigns where id=$cid and type ='MX')

Answer 5

Others have thrown in SQL methods. Since you've also tagged this with php, I'll also point out you can use the usort function to define a custom comparison function to be used by the sorting algorithm.

 // returns 1 if lhs > rhs, returns 0 if lhs == rhs, returns -1 if lhs <rhs
 function customCompare($lhsRow, $rhsRow)
 {
     $lhsType = $lhsRow['type']
     $rhsType = $lhsRow['type']
     // special case code so that 'MX' is always > then anything (except for another MX)
     if ($lhsType == 'MX')
     {
          if ($rhsType == 'MX')
          {
               return 0;
          }
          else
          {
               return 1;
          }
     }

     // return 1 if lhs > rhs
     if ($lhsType > $rhsType)
     {
         return 1;
     }
     else if ($rhsType < $lhsType)
     {
         return -1;
     }
     else
     {
         return 0;
     }
 }

 $rows = /*perform query without ORDER*/
 usort($rows, "customCompare");

Answer 6

you may want to use UNION

SELECT id, title, type
FROM campaigns
WHERE id={$cid}
AND type != 'MX'
ORDER BY type

UNION

SELECT id, title, type
FROM campaigns
WHERE id={$cid}
AND type == 'MX'

and run it as one query.

Answer 7

...
order by case when type = 'MX' then 1 else 0 end, type

would be portable to other databases (e.g. postgresql)