Mysql Rank / Row Number Query

Posted on 2006-04-07
Last Modified: 2008-02-01
I want to find out the "ranking" of a team based on their number of points.

id   name   points
1    test     100
2    test2    50
3    test3    150

I would like to run a query like "SELECT (...) as rank FROM teams WHERE id=2" and have it return 3

THis make sense?
Can this be done?

Question by:guitarclap
    LVL 30

    Expert Comment

    SELECT count(*) FROM teams WHERE points <= (SELECT points FROM teams WHERE id=2);
    LVL 37

    Accepted Solution

    I think this: ( >= instead of <= )
     SELECT count(*) FROM teams WHERE points >= (SELECT points FROM teams WHERE id=2);
    LVL 37

    Assisted Solution

    by:Harisha M G
    Or this: (in case the points are same for many teams)

    SELECT count(*)+1 FROM teams WHERE points > (SELECT points FROM teams WHERE id=2);
    LVL 1

    Author Comment

    So by this you are counting the number of teams that have more points then the team then adding 1.  So if there are 9 teams with more points than the team wanted then they are #10. Perfect!

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now