Sort VARCHAR database records with semicolon

Asked

Viewed 57 times

0

How can I sort recorded database records as SWEEP. I tried unsuccessfully the code below. 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
  • 2

    You can convert to number (float or decimal for example) to sort. For this you can use cast or convert: https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html Now why use a field varchar to store a numerical value?

  • 1

    I will check the possibility of changing the field to float.

  • 1

    Read this post https://answall.com/questions/288357/comort-do-a-soma-dos-values-of-a-columna-sum%C3%A9m-o-decimal-no-php? noredirect=1#comment588048_288357

2 answers

2


If the expected sorting is by numerical value, you can do the conversion only in the 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

  • For the field defined as VARCHAR this was the solution, but I updated the field to decimal(10,2) as directed by Ricardo Pontual, updated the values and just ordered normally.

1

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

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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.