how to do a query by ordering an ascending and a descending column

Asked

Viewed 96 times

0

need to do a query that searches for a column increasing and in the same query another column descending for example in the result below:

item   | quantidade
carro  |    400
carro  |    340
dado   |    240
disco  |    180
disco  |    120
faca   |    89
faca   |    59

I would like to see the result of this follow my query below:

SELECT * FROM qe.etiquetas_recortadas_aoi ignore index(PRIMARY) 
where line = 'AOI-1' 
and brand = 'NBK'  
and material_name_crop <> 'Borrada_1'  
order by qty_pass desc, material_name asc;
mysql sql

  • what is your mistake? I believe that in this way the order by work.

  • 2

    the syntax is correct. Your example of result does not match with select (item, quantity does not appear in query), but I believe it is material_name and qty_pass. If it is, just change the order by for order by material_name asc, qty_pass desc, but I couldn’t understand your problem....

  • @Ricardopunctual I also thought it was this, so much so that I answered and had not even seen your comment ! rs ... went by qty_pass be the int and material_name the string of the example he gave, but the problem is that the example is ordered by the 2, and that is where he must have got confused in his.

  • It seems to be just that, just a matter of reversing the fields, funny that he got to the order by and not try to change the order of the camps :)

  • Just correcting, String nay, varchar right ! rs

1 answer

2

The way you’re ordering:

ORDER BY qty_pass DESC, material_name ASC

You’re ordering first by qty_pass, thus, if it does not have equal values in the qty_pass, then will never order the material_name.

Example:

quantidade | item
    400    | carro
    340    | disco
    240    | dado
    180    | carro
    120    | disco
     89    | faca
     59    | faca

Reversing the orders:

ORDER BY material_name ASC, qty_pass DESC

This way, it will order first by material_name and then by qty_pass.

Example:

item   | quantidade
carro  |    400
carro  |    180
dado   |    240
disco  |    340
disco  |    120
faca   |     89
faca   |     59

In the ORDER BY, the order of the factors changes the result.

ORDER BY condicao1, condicao2 DESC, condicao3 ASC, ...

When you have equal values in the first condition (already ordered), it will sort the second, and so on, in the given sequence.

When it is not determined ASC or DESC, by default, will sort ASC

Browser other questions tagged mysql sql

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