CODEWITHSUNDEEP
mysql
Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

Mysql query to get the row position in a table

I have table with id (store user id) and score in different match. I want what is the position of a user.

So for i try this sql fiddle; in this I am getting all the row but I need only user having id 3 and it position in the table.

like this:

Score  Postion
26        3

Even i try to do like this but no success

  1. MySql: Find row number of specific record
  2. With MySQL, how can I generate a column containing the record index in a table?

Upvotes: 1

Views: 2154

Answers (3)

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

I got the answer: http://sqlfiddle.com/#!2/b787a/2

select * from (
select T.*,(@rownum := @rownum + 1) as rownum from (
select sum(score) as S,id from mytable group by id order by S desc ) as T 
JOIN    (SELECT @rownum := 0) r 
) as w where id = 3

Updated sqlfiddle and above query. Now it is working perfectly.

Upvotes: 2

dbarnes
dbarnes

Reputation: 1833

just add a where clause

select x.id,x.sum,x.rownum
from(
select id,sum(score) as sum,(@rownum := @rownum + 1) as rownum 
from mytable 
JOIN    (SELECT @rownum := 0) r
group by id
  ) x
where id =3

Upvotes: 0

Tobias Roland
Tobias Roland

Reputation: 1223

I think this should do the trick:

SELECT totalScore, rownum FROM (
    SELECT id,sum(score) AS totalScore,(@rownum := @rownum + 1) AS rownum
    FROM mytable 
    JOIN (SELECT @rownum := 0) r
    group by id) result
WHERE result.ID = 3;

Upvotes: 0

Related Questions