CODEWITHSUNDEEP
mysqlsql
Mastergalen
Mastergalen

Reputation: 4389

Get rank within table for any number

I have a bidding system in place. The user enters how much he wants to bid, which then sends a request via ajax to a PHP script, which then gets what rank that bid would place under the existing bids, and then displays it back to the bidder. This allows him to increase his bid to get the rank he wants.

For example

+-----------+------------+
| bidder_id | bid_amount |
+-----------+------------+
|  1        | 20         |
|  2        | 20         | 
|  3        | 30         |
|  5        | 40         |
|  6        | 10         |
+-----------+------------+

The user bids 15$, the query gets the rank as 5th.

How would this query look like? Is is possible to insert a fake row with the new user's bid and then order everything?

Upvotes: 0

Views: 47

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

Something simple like this should do it;

SELECT COUNT(*)+1 rank
FROM bids
WHERE bid_amount > 15

An SQLfiddle to test with.

Upvotes: 2

Related Questions