Mysql SELECT query used to find postion

Question

enter image description here

In the above table, c position is 3.

Is there any function to find the position in Mysql? enter image description here

update

Did you try these solutions? [MySQL Row Number](http://stackoverflow.com/questions/3126972/mysql-row-number) and [MySQL: Getting a row number (ranking) for a specific row](http://stackoverflow.com/questions/504116/mysql-getting-a-row-number-ranking-for-a-specific-row) — melihcelik, Nov 26 '11 at 15:34
@Srinivas Reddy Thatiparthy: say user b,on total number he is second because second highest. — , Nov 26 '11 at 15:35
possible duplicate of [ROW_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) — sll, Nov 26 '11 at 15:36

score 2 · Answer 1 · answered Nov 26 '11 at 15:33

2

try

select @rownum:=@rownum+1 ‘theposition’, t.* from mytable t, (SELECT @rownum:=0) r order by t.USER desc;

answered Nov 26 '11 at 15:33

Yahia

69,653
9
115
144

:I'ii input the user id,it has to find the postion in the whole table and say for a particular user what is his position in the table. – Nov 26 '11 at 15:55

Yanki Twizzy · Accepted Answer · 2011-11-26T16:26:25.637

1

Use this:

    SELECT x.user, 
       x.position,
       x.number
  FROM (SELECT t.user,
               t.number,
               @rownum := @rownum + 1 AS position
          FROM TABLE t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.number DESC) x
 WHERE x.user = 'c'

You can then get his position from x.position

edited Nov 26 '11 at 16:26

answered Nov 26 '11 at 15:37

Yanki Twizzy

7,771
8
41
68

:Say there is one more record user d and his number is 45 then i should get position as 3 but your query will give me as 4 so,it is wrong. – Nov 26 '11 at 16:01
@user1051322 I don;t understand why you are saying it's wrong. My query does not use the number column to determine row position in any way – Yanki Twizzy Nov 26 '11 at 16:15
:create a dummy table and i/p the data as i have given and input x.user = d,in db ,d ,number is 35.the o/p should be 3.third highest in the whole table – Nov 26 '11 at 16:20
Ok now I get. I didnt know you wanted the data sorted as well. Well I added something to the query (ORDER BY t.number DESC). This should solve the issue – Yanki Twizzy Nov 26 '11 at 16:27
what does @rownum: means in mysql.Can you explain your query – Nov 26 '11 at 16:32
@ROWNUM is a pseudo column, a pseudo column means that, the column actually does not exist in storage, whenever a query is executed, a new column is associated with the resulting set of rows, and the value contained is row number Please could you explain you last statement more. I don't really understand – Yanki Twizzy Nov 26 '11 at 16:42

score 1 · Answer 3 · answered Nov 26 '11 at 15:38

1

I recommend you to add an id field. i don't know of any function to do that. you can select * and then in a script language search it or use a subquery. but i don't recommend that.

answered Nov 26 '11 at 15:38

tomas

53
1
7

If the id is auto increment, it won't necessarily give you the position once you have deleted a row from the table. – Yanki Twizzy Nov 26 '11 at 15:59
@yankitwizzy:id is not auto increment. – Nov 26 '11 at 16:19

score 1 · Answer 4 · answered Nov 26 '11 at 15:38

1

SELECT 
     @rowNumber := @rowNumber + 1 'RowNumber', 
     u.* 
FROM Users u, 
  (SELECT @rowNumber := 0) r
  ORDER BY u.User ASC

answered Nov 26 '11 at 15:38

sll

61,540
22
104
156

:See my above image.I'ii input the user.what is reonumber? – Nov 26 '11 at 15:49

Mysql SELECT query used to find postion

4 Answers4