CODEWITHSUNDEEP
phpmysqlsql
user533178
user533178

Reputation: 77

MYSQL get row rank

I have a mysql table and I need to get random row and get the rank of total view

+--------+------------+---------+
| id     | name       |totalview|
+--------+------------+---------+
| 1      | ex1        |   20    |
| 2      | ex2        |   100   |
| 3      | ex3        |   30    |
| 4      | ex4        |   40    |
+--------+------------+---------+

for example : 

SELECT * FROM `table` WHERE `id` = '$rand';

$rand may be 1 or 2 etc ..

I need to get rank of this row by totalview

thank's

Upvotes: 0

Views: 902

Answers (2)

Explosion Pills
Explosion Pills

Reputation: 191729

SELECT SUM(ref.totalview < t.totalview) FROM t1 CROSS JOIN t1 ref WHERE t1.id = '$rand'

Upvotes: 0

zerkms
zerkms

Reputation: 254916

SELECT *,
       (SELECT COUNT(*) FROM table t2 WHERE totalview > t1.totalview ) + 1 cnt
  FROM table t1
 WHERE id = '$rand';

Upvotes: 2

Related Questions