CODEWITHSUNDEEP
phpmysql
JW500
JW500

Reputation: 37

Why won't results show in order when using count(*) DESC

I am trying to get results and list them in order descending by score. Everything works fine except that the last result is not in order for some reason as you can see below. What might be causing this last record from not listing in order?

Name  Score
Jay      19
Chris    12
Locke    1
Mike      3

Here is the relevant code:

$results = $dbh->prepare("select 
wp_users.ID,
wp_users.display_name,
points.ID,
points.PID,
SUM(points.PID) AS total
FROM points
LEFT JOIN wp_users ON points.ID=wp_users.ID
GROUP BY points.ID ORDER BY count(*) DESC 
LIMIT 4");

Upvotes: 1

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Presumably, you want to sort by the total:

order by total desc

or:

order by sum(points.pid) desc

The ordering you get is based on the number of matching records, rather than the sum in the output.

Upvotes: 3

Related Questions