mysql get row position of unique id

Question

I have a RANK table like this

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| uid         | VARCHAR(50) unsigned | NO   | PRI | NULL    |               |
| steps       | int(20) unsigned    | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

and I want to sort steps by DESC and get row position of specific row using uid which is primary.

I can sort them by

SELECT uid, steps FROM RANK ORDER BY steps DESC

but I have no clue how I can get a row position of specific row using uid. I saw some threads on stackoverflow and tried it on our DB server but it didn't work out well. Is there any way to do this?

I tried:

SET @rank=0; 
SELECT @rank:=@rank+1 AS rank, steps, COUNT(*) as ordercount 
FROM RANK 
GROUP BY steps 
ORDER BY ordercount DESC;

but it doesn't get my any result

with sequelizejs

async function getUserData(uid) {
    var query = ""
    query += "SELECT rank, total_steps_value FROM ("
    query += "    SELECT @rank := @rank + 1 AS rank, user_id, total_steps_value"
    query += "    FROM RANK"
    query += "    CROSS JOIN (SELECT @rank := 0) AS var"
    query += "    ORDER BY total_steps_value DESC) AS x"
    query += `WHERE user_id = ${mysql.escape(uid)}`

    let res = await models.sequelize.query(query)
    console.log("RES", res)
}

I get this error

SequelizeDatabaseError: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_id = '10208360871964156'' at line 1

See https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select for information on adding a row number to your select statement. — kainaw, Jan 22 '18 at 20:43
@kainaw I already looked at the thread but couldn't figure out how i can apply those answers to this situation since column types are different :( — Phillip YS, Jan 22 '18 at 20:47
Show what you tried and we can help you fix it. We're not going to write it for you. — Barmar, Jan 22 '18 at 20:48
@Barmar I tried `SET @rank=0; SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount FROM orders GROUP BY itemID ORDER BY ordercount DESC;` and this one looks like it doesn't have any value I can put into, especially primary id to find the row. — Phillip YS, Jan 22 '18 at 20:53

Barmar · Accepted Answer · 2018-01-22T21:04:14.157

2

You don't need COUNT(*), since you're not trying to rank by a count, you just want to rank by a column value.

SELECT @rank := @rank + 1 AS rank, uid
FROM RANK
CROSS JOIN (SELECT @rank := 0) AS var
ORDER BY steps DESC

To get the rank of a specific uid, put this in a subquery.

SELECT rank FROM (
    SELECT @rank := @rank + 1 AS rank, uid
    FROM RANK
    CROSS JOIN (SELECT @rank := 0) AS var
    ORDER BY steps DESC) AS x
WHERE uid = '123545'

edited Jan 22 '18 at 21:04

answered Jan 22 '18 at 20:58

Barmar

741,623
53
500
612

It did work but how could I get a rank of specific row by uid? – Phillip YS Jan 22 '18 at 21:00
Updated the answer. – Barmar Jan 22 '18 at 21:02
Thanks for update, but the rank value goes up so It gets messed up if I run more than one time. how can i avoid it? – Phillip YS Jan 22 '18 at 21:02
Sounds like you're not setting `@rank` back to `0` each time. I've updated the answer to show how to do this in the query itself rather than as a separate query. – Barmar Jan 22 '18 at 21:04
I get syntax error when I run this on server side with sequelize, I updated the question about it! – Phillip YS Jan 22 '18 at 21:39
You need a space before `WHERE`. Your SQL looks like `AS xWHERE uid` – Barmar Jan 22 '18 at 21:42
1

A simple `console.log(query)` would have shown that. – Barmar Jan 22 '18 at 21:44

score 0 · Answer 2 · answered Jan 22 '18 at 21:07

0

You can also get the rank of a particular row using:

select count(*)
from rank r
where r.score >= (select r2.score from rank r2 where r2.uid = ?);

answered Jan 22 '18 at 21:07

Gordon Linoff

1,242,037
58
646
786

I just updated the question about sequelize syntax error, I'd be really appreciated if you answer this too! – Phillip YS Jan 22 '18 at 21:41

mysql get row position of unique id

2 Answers2