Sort result using CASE and BETWEEN

Question

In the first code just below, I can list the freight price of the table transport via the band cep and peso, however I am having a problem, I need to sort the result according to how cep sent, depending on the query this falling in the second line and I need it to be in the first, I am not knowing how to use the function CASE along with BETWEEN for the first position later in the clause ORDER BY

This way it works right, without the CASE

SELECT

`Transportadora`.*,

FROM `envio_transportadora` AS `Transportadora` 
    INNER JOIN `shop_envio` AS `ShopEnvio` 
ON (`ShopEnvio`.`id_envio` = `Transportadora`.`id_envio_default` AND `ShopEnvio`.`ativo` = 'True' AND `ShopEnvio`.`id_shop_default` = '5')  
    WHERE 
'78000-000' BETWEEN `Transportadora`.`cep_inicio` AND `Transportadora`.`cep_fim` AND 13 BETWEEN `Transportadora`.`peso_inicial` AND `Transportadora`.`peso_final`

Already with the CASE of the syntax error in query goes below:

SELECT

`Transportadora`.*,

((CASE WHEN '78000-000' BETWEEN `Transportadora`.`cep_inicio` AND `Transportadora`.`cep_fim`)) AS cep_ordenar 

FROM `envio_transportadora` AS `Transportadora` 
    INNER JOIN `shop_envio` AS `ShopEnvio` 
ON (`ShopEnvio`.`id_envio` = `Transportadora`.`id_envio_default` AND `ShopEnvio`.`ativo` = 'True' AND `ShopEnvio`.`id_shop_default` = '5')  
    WHERE 
'78000-000' BETWEEN `Transportadora`.`cep_inicio` AND `Transportadora`.`cep_fim` AND 13 BETWEEN `Transportadora`.`peso_inicial` AND `Transportadora`.`peso_final`

Answer 1

You are making the use of CASE in the wrong way, the correct one would be something like this:

CASE condicao
 WHEN valor1 THEN 'valor1'
 WHEN valor2 THEN 'valor2'
 ELSE 'valor_alternativo'
END

And even then, there is no need to use this to sort data, moreover, in mysql there are other ways to make use of conditions such as use of IF, IFNULL, etc. Example:

 IF('condicao', valor_if_dondicao, 'valor_else_condicao') AS saida

To sort you don’t need any of this, just use ORDER BY at the end of your query and put the order preference list, example:

ORDER BY `Transportadora`.`cep_inicio` ASC, `Transportadora`.`cep_fim` ASC

Or make an order by more customized by the field:

ORDER BY FIELD (`Transportadora`.`cep_inicio`,'78000-000','79000-000','...') ASC,
         FIELD (`Transportadora`.`cep_fim`,'...','...') ASC

Answer 2

I believe that you do not know how to use CASE. With case we can buy a certain value and have a result according to this value. For example:

CASE numero
  WHEN 1 THEN 'um'
  WHEN 2 THEN 'dois'
  ELSE 'outro'
END

Thus we can compare a column, which in the case was number, to several values and depending on the value return another specific value. Besides, I didn’t see any ORDER BY clause.

Go to my blog https://oraclepress.wordpress.com which has several SQL things