mysql query to find row number of particular record from result set

Question

I have a table team (name,id,points).

i want to find the rank of team based on points.

SELECT *FROM team ORDER BY points DESC

above query sorts result in descending order of points of team. now i want to find the rank of particular team. means the row number of this result set.

It isnt a duplicate of the question you linked to. The other question wants the rank of the row when only selecting one row. Similar, but different. — Jeff, Jan 07 '14 at 14:37

score 1 · Accepted Answer · edited Jan 07 '14 at 11:16

1

easy way: Count all the teams with more (or equal) points than your desired_team

SELECT Count(*) 
FROM   team 
WHERE  points >= (SELECT points 
                  FROM   team 
                  WHERE  name = "team_name");

(you need to include your team so the "list" start with 1.) in that case if your team has the same points than another team this row will say your team is under. if you whant only to list the teams with more points use this:

SELECT Count(*) + 1 
FROM   team 
WHERE  points > (SELECT points 
                 FROM   team 
                 WHERE  name = "team_name");

pd: sorry for my english

edited Jan 07 '14 at 11:16

Hawk

5,060
12
49
74

answered Jan 07 '14 at 10:30

Melon

874
8
17

if u whant a more permanent search implement your sql: order by in a view. – Melon Jan 07 '14 at 10:34
that's exactly what i wanted..thanks. – HungryDB Jan 07 '14 at 10:51

score -1 · Answer 2 · answered Jan 07 '14 at 10:23

-1

Try this. It should give you exactly what you need.

SELECT 
    @i:=@i+1 AS rownumber, 
    t.*
FROM 
    team AS t,
    (SELECT @i:=0) AS test
ORDER BY points DESC

answered Jan 07 '14 at 10:23

peter.petrov

38,363
16
94
159

score -1 · Answer 3 · answered Jan 07 '14 at 10:24

-1

Try this:

SELECT IF(@prev <> points, @s:=@s+1, @s:=@s) AS `row_number`, 
  id, name, @prev:=points points
FROM
  team, (SELECT @s:= 1, @prev:= 0) s
ORDER BY points DESC

answered Jan 07 '14 at 10:24

Aziz Shaikh

16,245
11
62
79

mysql query to find row number of particular record from result set

3 Answers3