Get the ranking just with COUNT(ID) and using having

Question

I've a query in which I get the ranking of an user.

The table looks like this (here is order by points):

-----------------
| user | points |
-----------------
  paul1   22000
  paul    13440
  paul5    1400
  paul2    1300
  paul3    1300
  paul4    1300
   .        .
   .        .
   .        .

SELECT user, points
FROM table1 
WHERE points>= 1300 
HAVING user <= 'paul3' OR points > 1300 
ORDER BY points DESC, user ASC

This query returns the correct data I need but I really need only the number of rows (5 in this example), so I tried modifying the query as below:

SELECT COUNT(ID) AS num, user, points
FROM table1 
WHERE points>= 1300 
HAVING user <= 'paul3' OR points > 1300 
ORDER BY points DESC, user ASC

But here it returns num = 6 and I don't understand at all why.

Any ideas?

Thanks so much!

Answer 1

Why are you using both a where and a having clause with no aggregation? Your query is equivalent to:

SELECT user, points
FROM table1 
WHERE (user <= 'paul3' and points = 1300) OR (points > 1300) 
ORDER BY points DESC, user ASC

If you want to count the rows, then just do count(*) in the select:

SELECT COUNT(*)
FROM table1 
WHERE (user <= 'paul3' and points = 1300) OR (points > 1300) 
ORDER BY points DESC, user ASC;

The reason your query doesn't do what you want is because the having clause is evaluated after the aggregation. It filters the result of the aggregation, not of the rows going into the aggregation.

Answer 2

Try counting all distinct users:

    SELECT COUNT(DISTINCT user) AS num
FROM table1 
WHERE points>= 1300 
HAVING user <= 'paul3' OR points > 1300 
ORDER BY points DESC, user ASC