Sort mysql query correctly

Asked

Viewed 56 times

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;

Imagem de exemplo

  • Kills and quantity are always 1 !? Overrides "order by" ...

  • 2

    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.

  • I believe sorting by cast(quantity as integer) DESC solves your problem.

  • 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!!!!

  • Matthew could answer the question so I could signal it as a better answer and thus help other people?

No answers

Browser other questions tagged

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