CODEWITHSUNDEEP
mysqlsqlrow-number
neneo
neneo

Reputation: 646

MySQL - How to get row number

i have table like this:

tb_bid_test
+-------------+---------------------+
| Field       | Type                |
+-------------+---------------------+
| username    | varchar(20)         |
| poin        | bigint(20)          |
+-------------+---------------------+

then i run this query :

select @r := @r+1 as rank, z.* from(SELECT username, sum(poin) as jumlahPoin FROM `tb_bid_test` GROUP BY username order by jumlahPoin desc limit 3 )z, (select @r:= 0)y;

and the result :

+--------+------------+--------------+
| rank   | username   |  jumlahPoin  |
+--------+------------+--------------+
|   1    |        neo |      500     |
|   2    |     andhie |      348     |
|   3    |       john |      123     |
+--------+------------+--------------+

i want to get 1 row with username andhie, like this:

+--------+------------+--------------+
| rank   | username   |  jumlahPoin  |
+--------+------------+--------------+
|   2    |      andhie|      348     |
+--------+------------+--------------+

or others username and get the rank too, if i use the code before and add where username ='andhie' the rank is always 1. How to get the current rank based on their points?

Upvotes: 0

Views: 297

Answers (3)

ydoow
ydoow

Reputation: 2996

Use nested query

SELECT *
FROM (
        SELECT @r := @r + 1 AS rank, z.*
        FROM (
                SELECT username, SUM(poin) AS jumlahPoin
                FROM tb_bid_test
                GROUP BY username
                ORDER BY jumlahPoin 
                DESC limit 3
        ) z,
        (SELECT @r := 0) y
    ) tbl
WHERE username = "andhie"

Upvotes: 0

Tharif
Tharif

Reputation: 13971

Select from your result username with value 'andhie'

create table tb_bid_test (username varchar(20),poin bigint(20));
insert into tb_bid_test values ('neo',500);
insert into tb_bid_test values ('andhie',348);
insert into tb_bid_test values ('john',123);


select * from (
select 
@r := @r+1 as rank, 
z.* 
from(SELECT username, sum(poin) as jumlahPoin FROM `tb_bid_test` 
GROUP BY username order by jumlahPoin desc limit 3 )z, 
(select @r:= 0)y 
  ) as t where username ='andhie'

Try the fiddle here

Upvotes: 1

D T
D T

Reputation: 3746

try this query:

Select * from (select @r := @r+1 as rank, z.* from(SELECT username, sum(poin) as jumlahPoin FROM `tb_bid_test` GROUP BY username order by jumlahPoin desc limit 3 )z, (select @r:= 0)y;) where username="andhie"

Upvotes: 0

Related Questions