CODEWITHSUNDEEP
phpmysqlsql
Lucas
Lucas

Reputation: 159

MySQL get row position in query

This is something i have been thinking for hours and still cant get the answer. I want to get the row position.

So, i have this mysql table called 'orders'

+-----------------------------+
+ id | bid                    +
+-----------------------------+
+  1 | 4.00                   +
+  2 | 6.00                   +
+  3 | 5.00                   +                 
+  4 | 4.00                   +
+-----------------------------+

And i have already managed to get the row position with this function

SELECT p.*, (SELECT count(*) FROM orders WHERE bid > p.bid) AS position FROM orders p

And the output is 

+-----------------------------+
+ id | bid      | position    +
+-----------------------------+
+  1 |     4.00 | 3           +
+  2 |     6.00 | 1           +
+  3 |     5.00 | 2           +
+  4 |     4.00 | 3           +
+-----------------------------+

Please note that if values are the same they have the same position.

So what i need now is a query where i select a new bid (that does not exist on the table) and tells me in which position this bid will be. It should be something like this: If my new bid is 7.00 in which position will it be? (answer: 1 position), another example: If my new bid is 4.00 in which position will it be? (answer: 3 position)

Im looking for this to be done in SQL. If it is not possible then i will like a php answer.

I hope you guys can help me on this, i have been burning my head the last hours and failed to get this done.

Thank you very much in advance.

Upvotes: 2

Views: 1827

Answers (3)

Arek S
Arek S

Reputation: 4719

How about simple 

SELECT count(*)+1 as position FROM orders WHERE bid > :new_bid

Upvotes: 0

xdazz
xdazz

Reputation: 160853

Get all distinct bid and append the rank, then join with it.

SELECT t1.*, t2.position
FROM orders t1
LEFT JOIN (
  SELECT bid, @i:=@i+1 AS position
  FROM (
    SELECT DISTINCT bid 
    FROM orders
    ORDER by bid DESC
  ) a, (SELECT @i:=0) b
) t2 ON t1.bid = t2.bid

THE SQLFiddle DEMO.

Upvotes: 0

valex
valex

Reputation: 24144

First of all I think you should add a DISTINCT keyword to your subquery. Because if we add bid=3 to the table it will get position = 5 with your query. So 4 will be missed.

SELECT p.*, 
      (SELECT count(DISTINCT bid)+1 
              FROM orders WHERE bid > p.bid) AS position 
      FROM orders p

SQLFiddle demo

Now we need to know position for new bid. Just use your subquery:

SELECT count(DISTINCT bid)+1 FROM orders WHERE bid > 4

Upvotes: 3

Related Questions