CODEWITHSUNDEEP
mysql
TobiasHH
TobiasHH

Reputation: 125

Get list of ranks between a range

I got a table which looks like this:

+-------------------+---------------------+
| Username(varchar) | Points(integer) |
+-------------------+---------------------+
| User #0           |                1000 |
| User #1           |                 900 |
| User #2           |                 900 |
| User #3           |                 810 |
| User #4           |                 800 |
| User #5           |                 790 |
| User #6           |                 750 |
| User #7           |                 749 |
| User #8           |                 730 |
| User #9           |                 650 |
| User #10          |                 600 |
| User #11          |                 550 |
| User #12          |                 450 |
+-------------------+---------------------+

I need a query where I can put 2 numbers in. For example I only want to see place 2 - 6. I hope you can tell me how I am able to do it. People can have the same amount of points. Place User #1 and User #2 got the same amount and so I need 6 rows not 5, if it would be easy I would use order by points and limit

The output should look like:

+-------------------+---------------------+-------------------+
| Username(varchar) | Points(integer)     | Rank              |
+-------------------+---------------------+-------------------+
| User #1           |                 900 |                 2 |
| User #2           |                 900 |                 2 |
| User #3           |                 810 |                 3 |
| User #4           |                 800 |                 4 |
| User #5           |                 790 |                 5 |
| User #6           |                 750 |                 6 |
+-------------------+---------------------+-------------------+

Upvotes: 0

Views: 128

Answers (1)

spencer7593
spencer7593

Reputation: 108500

Here's an example using MySQL user-defined variables to calculate "rank", assigning the same "rank" value to "ties" for points.

  SELECT r.username
       , r.points
       , r.rank
    FROM ( SELECT t.username
                , @rank := IF(t.points=@prev_points,@rank,@rank+1) AS rank
                , @prev_points := t.points AS points
             FROM mytable t
            CROSS
             JOIN ( SELECT @rank := 0, @prev_points := NULL ) i 
            ORDER BY t.points DESC, t.username DESC
         ) r
   WHERE r.rank BETWEEN 2 AND 6
   ORDER BY r.points DESC, r.username DESC

Original answer to original

You can use a LIMIT clause in combination with an ORDER BY.

For example:

  SELECT t.username, t.points
    FROM mytable t
   ORDER BY t.points DESC, t.username DESC
   LIMIT 4,6

In this example, the LIMIT clause is saying "skip the first four rows and return the next six rows". So, this would return rows starting with the fifth row, up through the tenth row.

Upvotes: 1

Related Questions