Sort string and ASC number

Asked

Viewed 136 times

1

I need to sort a table upwards, but the target column has numbers (1,2,3,4,5 in String format) and letters (P,M,G,GG).

How can I do a query that first sorts the strings (G,M,P) in ascending order, and then sorts the numbers (1,2,3,4...) ?

I tried with the CAST:

SELECT * FROM valor_atributo ORDER BY CAST(valor)

This way he was able to order only the strings with the numbers, but the strings with letters he did not order correctly.

I need to sort the column (value) first the strings (G,M,P) and then the numbers (1,2,3,4)...

mysql database

1 answer

2


You can use it like this:

SELECT * FROM valor_atributo ORDER BY valor * 1, valor ASC

See here working: Sqlfiddle

It was based on this question, where I had a solution to an almost identical problem: how-to-order-mysql-varchar-Results

  • That’s what I needed. Thanks, you could explain better about the value * 1?

  • 1

    yes, when you perform the operation of a string * 1, for example 'A' * 1 results in zero (0), that is, the letters will always come before the numbers using this in the order by to test, can perform this: select 'A'*1, 'B'*1, 'z'*1, 1, 2

  • 1

    ah, one detail that occurred to me is that, if the number is negative will invaildar this logic, then you need to take the absolute value without signal, then in the query you need to use the function abs: SELECT * FROM dados ORDER BY abs(valor * 1), valor ASC

Browser other questions tagged mysql database

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