Sort database records in VARCHAR with semicolons

Question

Sort database records in VARCHAR with semicolons

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

How can I sort records from saved databases like VARCHAR . I tried the code below without success. How can I format to get the expected order?

SELECT *, CAST(representantes_vendas.valor AS INT)  FROM representantes_vendas
INNER JOIN representantes ON representantes_vendas.codigo_repre = representantes.codigo  
ORDER BY  representantes_vendas.valor DESC 


//Resultado Obtido
2017    99.948,54
2017    99,27
2017    91,10
2017    97.757,23


//Resultado esperado
2017    99.948,54
2017    97.757,23
2017    99,27
2017    91,10

mysql

asked by anonymous 09.04.2018 / 14:42

2 answers

1

Considering that your Brazilian notation is that the point would only be for formatting, you can remove it in ordering:

SELECT *, CAST(representantes_vendas.valor AS INT)  FROM representantes_vendas
INNER JOIN representantes ON representantes_vendas.codigo_repre = representantes.codigo  
ORDER BY replace(representantes_vendas.valor, '.','') DESC

09.04.2018 / 14:46

My session is not being destroyed -PHP- Query Database between tables

score 2 · Accepted Answer

If the expected ordering is by numeric value, you can only convert to ORDER BY . Example:

SELECT *, CAST(representantes_vendas.valor AS INT)  FROM representantes_vendas
INNER JOIN representantes ON representantes_vendas.codigo_repre = representantes.codigo  
ORDER BY CAST(REPLACE(REPLACE(x.valor, ".", ""), ",", ".") AS DECIMAL(20, 2)) DESC