Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

SQL ranking users with row limit

Codes here !

Moderators: macek, egami, gesf

SQL ranking users with row limit

Postby iam4423 » Wed Oct 17, 2012 7:12 am

i have this query

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.
iam4423
New php-forum User
New php-forum User
 
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

Return to mySQL & php coding

Who is online

Users browsing this forum: Bing [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron