Sort result using CASE and BETWEEN

Navigation
1

In the first code just below, I can list right the freight price of the transports table via the range of cep and peso , however I'm having a problem, I need to sort the result according to zip sent, depending on the query is falling on the second line and needs to be in the first line, I am not sure how to use the CASE function along with BETWEEN to return it to the first position afterwards in the ORDER BY

This works fine, without 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 CASE of syntax error in query 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'
    
asked by anonymous 22.07.2015 / 04:32

2 answers

2

You're making CASE the wrong way, the correct one would look 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, in addition, in mysql there are other ways to make use of conditions like the use of IF, IFNULL, etc. Example: 

 IF('condicao', valor_if_dondicao, 'valor_else_condicao') AS saida
To order you do not need any of this, just use ORDER BY at the end of your query and place the preference list of the order, example: 
ORDER BY 'Transportadora'.'cep_inicio' ASC, 'Transportadora'.'cep_fim' ASC

Or even make a more personalized order by the field: 

ORDER BY FIELD ('Transportadora'.'cep_inicio','78000-000','79000-000','...') ASC,
         FIELD ('Transportadora'.'cep_fim','...','...') ASC
    
22.07.2015 / 15:11
-1

I believe you do not know how to use CASE. With the 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

So we can compare a column, which in case was number, to several values and depending on the value return another specific value. Otherwise I did not see any ORDER BY clause.

Access my blog link that has several things in SQL

    
22.07.2015 / 14:59
Compiling with g ++ Maximum number of concurrent connections exceeded in Apache HTTP