-1

My situation is a bit more complicated than order-by-enum-field-in-mysql

My SQL is:

SELECT `a`.`ChannelID`,
  `a`.`CategoryID`,
  `a`.`Country`,
  `a`.`LocalName`,
  if((`a`.`Type` = 'FreeText'
      AND a.IsHybrid = 'YES'), 'Hybrid', 
      if(`a`.`Type` = 'FreeText'
        AND a.IsHybrid = 'NO', 'FreeText', a.Type)) AS TYPE,
  `a`.`IsHybrid`
FROM `ProCatCountry` AS `a`
  INNER JOIN `ProCat` AS `b` ON a.CategoryID = b.CategoryID
ORDER BY FIELD(a.TYPE, 'FreeText', 'Hybrid', 'Structured') ASC

But the order is not correct, first FreeText records appeared then Hybrid then FreeText appeared again.

Field TYPE is enum, and I want to order by this field, meanwhile, a value which does not included in enum list - the calculated one called Hybrid should also be ordered by correctly. How can I do with this situation?

Originally, there was a TYPE field ambiguous error, thanks to @Jens, after changed to a.TYPE, the ambiguous error was resolved.

Community
  • 1
  • 1
Phoenix
  • 1,055
  • 1
  • 12
  • 27

1 Answers1

0

You have to add the table alias in order by statement:

ORDER BY FIELD(a.TYPE, 'FreeText', 'Hybrid', 'Structured') ASC

Becuase it is a column of both tables.

Jens
  • 67,715
  • 15
  • 98
  • 113
  • Hey, the ambiguous error vanished after I added to `a.TYPE` but new problem appeared, I've modified my question, please take a look, Thank you! – Phoenix Feb 09 '15 at 08:32
  • @Phenix It is not a good idea to exit the question. You should update your question that the community see that the question has changed. So it is better to let the original question visible and add a checktion `UPDATE` – Jens Feb 09 '15 at 08:34
  • Oh, sorry, how can I update the question? I've just edited it. – Phoenix Feb 09 '15 at 08:35
  • @Phoenix You missunderstand me. You should use the edit function. But you should not override the original information. You shout add the update after the original question. – Jens Feb 09 '15 at 08:40
  • Now I understand, OK! I made a sqlfiddle here: http://sqlfiddle.com/#!2/02a423/1, please see – Phoenix Feb 09 '15 at 08:45