1
I have a query to fetch some values, however the value does not return me correctly. In the example below you will notice that user 1 is below user 2. In this case the order should be in this sequence > level > Kills/quantity > Exp.
Example 2:
Renato 1 place (level: 105, Exp: 90, Kills/quantity: 100) = level esta 105 and Kills/quantity 100 so the rest are below the 2 main requirements.
Peter 2 place (level: 105, Exp: 500, Kills/quantity: 90) is the same level of 1 place but first place has 10 more Kill.
Pietro 3 place (level: 105, Exp: 1000, Kills/quantity: 80) = this has the same level as 1 and 2 but has more Exp but has 10 Kills/less.
Gustavo 4 place (level: 90, Exp: 700, Kills/quantity: 70) = that has level 90 600 Exp this in 4 place.
Renan 5 place (level: 80, Exp: 800, Kills/quantity: 60) = this has level 80 has more Exp than 4 place but has less Kills/quantity.
Final example:
Seeing the image below the order would be:
id: 241
id: 591
id: 125
id: 166
id: 144
SELECT player.id,player.`name`,player.`level`, player.job,player_index.empire,player.exp,IFNULL(player.quantidade,0) as kills, player.quantidade FROM player LEFT JOIN player_index ON player_index.id=player.account_id LEFT JOIN kill_system ON player.id=kill_system.id WHERE player.`name` NOT LIKE '[%]%' OR player.`name` LIKE '%[VIP]%' ORDER BY player.`level` DESC, quantidade DESC, player.playtime DESC, player.exp DESC;
Kills and quantity are always 1 !? Overrides "order by" ...
– Motta
What is the type of data in the quantity column? It looks like it’s varchar, if it really is that, the ordering really will get wrong, it needs to be of a numerical type to reproduce the result you expect.
– mateusalxd
I believe sorting by cast(quantity as integer) DESC solves your problem.
– anonimo
Matthew you’re a genius and yet you opened my mind! it was very simple, I reviewed the structure of my bank and I noticed that it was really varchar and now it’s all right!!!!
– Renato
Matthew could answer the question so I could signal it as a better answer and thus help other people?
– Renato