Scooter Daraf Scooter Daraf - 5 months ago 9
SQL Question

Order by asc and desc didnt give right result

I have a problem getting result in my query .

id ,voteup , votedown

1 , 12 , 7
2 , 6 , 1
3 , 4 , 9
4 , 0 , 6


and my query is

SELECT cm.id ,cm.voteup,cm.votedown FROM Table1 cm
INNER JOIN ( select id ,MAX(voteup - votedown ) as maxe
from Table1 where voteup - votedown >= 5 group by id ) tt
on cm.id = tt.id
ORDER BY cm.voteup DESC,cm.votedown asc,cm.id limit 1


Im getting this result

id, voteup , votedown
1, 12 , 7


I want to select the max result which have
voteup - votedown >= 5
first
And if two result have same result i want to order the one which have less votedown

Im wishing having this result

id , voteup , votedown
2 , 6 , 1


thanks.

Here is sqlfiddle Demo

I have tried to change the order of the columns like that

ORDER BY cm.votedown asc ,cm.voteup DESC limit 1


But this also gives me wrong result if i have values like that

id ,voteup , votedown

1 , 6 , 0
2 , 9 , 2
3 , 4 , 9
4 , 0 , 6


which gives

1, 6 , 0


But i want

2 , 9 , 2 > which is max (9-2)


fiddle for this last try

Answer

You have to place field maxe DESC first, followed by cm.votedown asc first in the ORDER BY clause:

SELECT cm.id ,cm.voteup,cm.votedown  FROM Table1 cm  
INNER JOIN ( 
   select id, MAX(voteup - votedown ) as maxe 
   from Table1 
   where voteup - votedown >= 5  
   group by id  
) tt on cm.id = tt.id
ORDER BY maxe DESC, cm.votedown asc, cm.voteup DESC, cm.id  limit 1

This query will pick the record having the biggest voteup - votedown difference. In case of ties, the query returns the record having the smallest votedown value.

Demo here