select all data with position of given id

Question

This is my table

   id   |   name   |  userid  |  score  |      date      |
   ------------------------------------------------------------
    1   |   john   |    1     |   44    |  2013-03-2 
    2   |   mary   |    2     |   59    |  2013-03-5  
    3   |   john   |    12    |   38    |  2013-03-8 
    4   |   elvis  |    3     |   19    |  2013-02-10 
    5   |   john   |    11    |   1002  |  2013-01-11
    6   |   johnah |    10    |   200   |  2013-01-11

I want to show number of position of my given userid order by highest score.

if i give userid 12 in my query then it show position number 5 for user 12 depend on highest score

Select all records from the database and use `ORDER BY score`. Then loop through the results and search for the given user ID. Count how many times the loop was entered before the user ID was found and you got your result. — t.h3ads, Apr 01 '14 at 08:22
http://stackoverflow.com/questions/12541040/how-to-calculate-rank-from-mysql-table — Hanky Panky, Apr 01 '14 at 08:22

score 1 · Answer 1 · answered Apr 01 '14 at 08:23

1

I think this is what you want.

SELECT COUNT(*)+1 FROM table_name WHERE userid!=12 AND 
score>(SELECT score FROM table_name WHERE userid=12);

answered Apr 01 '14 at 08:23

CodeBird

3,883
2
20
35

if i give userid 12 in my query then it show position number 5 for user 12 depend on highest score – user3484136 Apr 01 '14 at 08:27
Isn't this what you want? I mean this query should work for you @user3484136 – CodeBird Apr 01 '14 at 08:31

score 0 · Answer 2 · edited Apr 01 '14 at 10:51

0

Try This :

SELECT *  
FROM table_name 
WHERE id =(SELECT max( userid ) FROM table_name)

edited Apr 01 '14 at 10:51

Taryn

242,637
56
362
405

answered Apr 01 '14 at 08:36

Benjamin

2,257
1
15
24

select all data with position of given id

2 Answers2