- Code: Select all
SELECT x.Id,
(
SELECT COUNT( points ) +1
FROM usr
WHERE points > x.points
) AS rank
FROM usr AS x
which returns the correct data from the database and i can add in a where claws to limit the results like so
- Code: Select all
SELECT x.Id,
(
SELECT COUNT( points ) +1
FROM usr
WHERE points > x.points
) AS rank
FROM usr AS x
WHERE x.Id = 10
the problem is if i want to limit my results by the rank, because it is not an actual field in the database i cannot use it in my where clause, what i want is something like this
- Code: Select all
SELECT x.Id,
(
SELECT COUNT( points ) +1
FROM usr
WHERE points > x.points
) AS rank
FROM usr AS x
WHERE rank
BETWEEN 10
AND 20
does anyone know of a good way of doing this, i dont mind doing multiple queries if i have to but the more optimised the better
Thanks
Matt.
