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

    rank from 
        @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.



2 Answers 2

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

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


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.

  • insert statement after the select statement Commented Dec 27, 2019 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.