CODEWITHSUNDEEP
mysqlcountsql-order-by
DisgruntledGoat
DisgruntledGoat

Reputation: 72510

How to order by a count(*) field?

I have a MySQL query like this:

SELECT xid, count(yid) AS tot
FROM x_y_map
WHERE z=11
GROUP BY xid
ORDER BY tot

The table is just a simple mapping of X's to Y's. Without the ORDER BY the query appears to work and displays the number of Y's each X has - one row for each X. However, adding the ordering, all I get is:

xid     yid
0       36503

Why is this happening, and how do I get the ordered list?

Upvotes: 0

Views: 232

Answers (2)

DisgruntledGoat
DisgruntledGoat

Reputation: 72510

On further research it looks like this is a bug in PHPMyAdmin. Running the same query through PHP gets the correct result. I think PMA is tripping up over so many rows for some reason.

Upvotes: 1

plandolt
plandolt

Reputation: 1931

try direct count in order by like that:

ORDER BY count(yid)

Upvotes: 4

Related Questions