MYSQL Calculate ranking of row that is not yet inserted

1 vote

2 answers

182 views

                          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!
                        

Asked by follmer (113 rep)

Dec 27, 2019, 05:42 AM
Last activity: Jun 30, 2025, 05:02 PM

MYSQL Calculate ranking of row that is not yet inserted

Related Questions