1

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?
Community
  • 1
  • 1
Suresh Kamrushi
  • 15,627
  • 13
  • 75
  • 90

3 Answers3

2

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.

Suresh Kamrushi
  • 15,627
  • 13
  • 75
  • 90
0

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
dbarnes
  • 1,803
  • 3
  • 17
  • 31
0

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;
Tobias Roland
  • 1,182
  • 1
  • 13
  • 35
  • same as other answer. i do not find any difference – Suresh Kamrushi Nov 16 '13 at 17:13
  • I didn't see the other answer before posting - I was too impressed with SQL-fiddle (never used that before, very useful tool - thank you for sharing it!) to notice that other answers have been posted. Anywho, I do spot one difference, though - the selection here leaves out the ID, so it matches your initial question. – Tobias Roland Nov 16 '13 at 17:15
  • query is not perfect, missing somewhere. updated sql fiddle link :http://sqlfiddle.com/#!2/5ff3e/25 – Suresh Kamrushi Nov 17 '13 at 11:50