4WebHelp
 FAQ  •  Search  •  User Groups  •  Forum Admins  •  Smilies List  •  Statistics  •  Rules   •  Login   •  Register
Toggle Navigation Menu

 My SQL ORDER BY is incorrect
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic
Author Message
brok21k
Junior WebHelper
Junior WebHelper


Joined: 14 Feb 2007
Posts: 1

PostPosted: Wed Feb 14, 2007 11:08 pm (10 years, 8 months ago) Reply with QuoteBack to Top

Im trying to sort by ratio (which is generated by (kills/deaths+1)).

SELECT DISTINCT name, SUM(kills), SUM(deaths), ROUND(SUM(kills) / (SUM(deaths)+1),2), COUNT(name)

FROM player WHERE name LIKE '%TuG%'

GROUP BY name


ORDER BY ROUND((SUM(kills) / (SUM(deaths)+1)),2) DESC, SUM(kills) DESC


I also used ORDER BY SUM(kills) / (SUM(deaths)+1) DESC, SUM(kills) DESC and added () around but it still doesnt work.

This almost works fine, apart from incorrectly displaying the ratio (kills/deaths+1) in order. There is ways round this (putting into a an array in php and sorting it there), but I want to use sql to correctly sort it.


Thses are my output of results (the db does have around 40000 records)
http://www.brok21k.co.uk/testsql.php


john
OfflineView User's ProfileFind all posts by brok21kSend Personal Message
adam
Forum Moderator & Developer



Joined: 26 Jul 2002
Posts: 704
Location: UK

PostPosted: Sat Feb 17, 2007 10:40 am (10 years, 8 months ago) Reply with QuoteBack to Top

I would suggest simplifying it to this:
Code:
SELECT DISTINCT name, SUM(kills), SUM(deaths), ROUND(SUM(kills) / (SUM(deaths)+1),2) as ratio, COUNT(name)

FROM player WHERE name LIKE '%TuG%'

GROUP BY name

ORDER BY ratio DESC

Not sure if that'll fix it or not, but either way there's no sense in doing that calculation twice.

________________________________
It's turtles all the way down...
OfflineView User's ProfileFind all posts by adamSend Personal MessageVisit Poster's Website
adam
Forum Moderator & Developer



Joined: 26 Jul 2002
Posts: 704
Location: UK

PostPosted: Sat Feb 17, 2007 10:44 am (10 years, 8 months ago) Reply with QuoteBack to Top

In fact, you might even be able to simplify it further:
Code:
SELECT DISTINCT name, SUM(kills) as s_kills, SUM(deaths) as s_deaths, ROUND(s_kills / (s_deaths +1) ),2) as ratio, COUNT(name)

FROM player WHERE name LIKE '%TuG%'

GROUP BY name

ORDER BY ratio DESC

Though I'm not sure if that is valid SQL.

________________________________
It's turtles all the way down...
OfflineView User's ProfileFind all posts by adamSend Personal MessageVisit Poster's Website
Display posts from previous:      
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic


 Jump to:   




You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot edit your posts in this forum.
You cannot delete your posts in this forum.
You cannot vote in polls in this forum.


Page generation time: 0.069104 seconds :: 17 queries executed :: All Times are GMT
Powered by phpBB 2.0 © 2001, 2002 phpBB Group :: Based on an FI Theme