MYSQL Calculate ranking of row that is not yet inserted

Question

I've got a table similar to this data:

| id | username | value |
|-----------|----------|-------|
| 1 | bob | 46 |
| 483 | alice | 90 |
| 176 | sue | 3001 |
| 82 | harry | 0 |
| 10493 | bill | 327 |

I have this query that returns me the ranking of a user based on their id

SELECT 
 username, 
 value, 
 rank from 
 (
 SELECT 
 tp.username, 
 tp.value,
 tp.id, 
 @curRank := @curRank + 1 AS rank
 FROM table tp, 
 (SELECT @curRank := 0) r
 ORDER BY tp.value DESC
 )
as temp
WHERE id = 483;

So for the above query, I would get a ranking returned of 4 for the id 483.

Let's say I want to insert the following row into the table:

| id | username | value |
|---------------------------|
| 2 | sally | 2000 |

Is there any way to know what rank this row will have after it is inserted, without actually inserting it?

I.e. sally would have a rank of 2 from the above query if inserted.

The reason I am curious if this is possible is that I'm attempting to insert the row into the database and only have this one transaction, rather than having to insert the row, then re-run the rank query.

Thanks!

Answer 1

BEGIN;
INSERT the row
calculate the rank; put it into an app variable
ROLLBACK;

The ROLLBACK lets you undo the INSERT. But meanwhile, you have grabbed the rank.

Answer 2

Where does the new data come from? Do you just manually type it?

If the data is coming from an application, you may declare a variable with its ID then before insert it, you make a select query that will count the rank of the new data before it's added to the table.

If you insert data one by one, then declaring variable is your best choice. You just need to change the value of the variables then you're good.

Declare Id int = 2
Declare username varchar(50) = 'sally'
declare value int = 2000

Select statement that will provide you the rank of the new data insert statement.

• Dexter Developer: insert statement after the select statement