How to sort by ORDER BY using also rowid?

Question

Python

Please help sorting.

when requested:

nume = cursor.execute("SELECT cash, rowid FROM users WHERE id = {}".format(ctx.author.id)).fetchone()[1]

I get: 730 (this is my place in the database without sorting)
But I would like to somehow sort this by cash and already get the rowid .
And to get exactly the value: 9 (screenshot with sorting)
I tried:

nume = cursor.execute("SELECT cash, rowid FROM users WHERE id = {} ORDER BY cash".format(ctx.author.id)).fetchone()[1]

get too: 730

Answer 1

But I would like to somehow sort it by cash and already get the rowid .

Do not confuse the concepts of
rowid - a fixed unique identifier for the records of a column that does not depend on sorting (but under certain conditions it can change, depending on the declaration).
"the place of the user in the table" at different sortings will be different.
So what is still required? Find out the user's rowid by id (which is ridiculous in itself, because the id itself must be unique, there is no point in searching for the rowid by it)? Or find the relative user number in a sorted list?
If the second - then there is a row_number, which indicates sorting. For example, you can get the user's place by voice activity

SELECT id, name, cash, row_number() over(order by cash desc) as rownum FROM users

And, accordingly, find the user's serial number with such sorting

cursor.execute("SELECT cash, rowid, rownum FROM (SELECT id, name, cash, row_number() over(order by cash desc) as rownum FROM users) WHERE id = ?", (ctx.author.id, )).fetchone()[2]

rowid and rownum will be different respectively (the 1st is the identifier in the table, the 2nd is the serial number in sorting). Those. for user with max. the amount of cash rownum will be equal to 1, and rowid depending on its identifier in the table.

Answer 2

If you need a rowid for some reason, you are most likely doing something wrong. What do you need it for?
In relational databases, the position in the table (not in the selection, but in the table) does not mean anything. You do not have to manage it and there is nothing to get this position for. I'm sure there are other ways to achieve what you're trying to achieve there. For example, if you need to find out how many users have a cache less than yours, then ask like this:

SELECT count(*) 
FROM users u 
WHERE u.cash < (SELECT cash FROM users WHERE id = :my_user_id)

If you need some rating of the poorest players up to and including you, then you can use sorting:

SELECT u.id, u.cash 
FROM users u 
WHERE u.cash <= (SELECT cash FROM users WHERE id = :my_user_id)
ORDER BY u.cash

The order of selection by index of identical values ​​is not guaranteed. If you need sample order stability, add a regular unique identifier as the second sort parameter, then people with the same cache will not change places from request to request.