php / mysql - how to get rankings of users?

for example i have a table like this :

name   rating
matei     124
andrei     20
serj       25
john  开发者_如何学编程    190
mike       96
andy      245
tom        73

i need to output something like this(order by rating):

john's position is 2; or, tom's position is 5; (i don't need to get all result , just one )

How can I achieve this?

Thanks in advance

---

Generally order of rows in a query result is not guaranteed by MySQL unless ordering is explicitly specified with ORDER BY clause. If you have some separate ordering column, you may use query like the following:

SELECT count(1) as position
FROM table
WHERE order_column <= {john's order_column value};

If you don't have ordering column, I'd recommend you to define first, what does "john's position" and "tom's position" mean.

UPDATE: AFAIU, you want to get position in list sorted by rating (sorry, I initially did not get it). So, rating would be your order_column. In this case, you should decide, how do you calculate position, if two guys have equal rating (who's position is higher?).

So, the query may look in the following way:

SELECT count(1) as position
FROM table
WHERE
    rating > (SELECT rating FROM table WHERE id={user's ID});

---

SELECT  COUNT(*) + 1
FROM    users
WHERE   (rating, name) <
        (
        SELECT  rating, name
        FROM    users
        WHERE   name = 'john'
        )

Note that if you will have duplicates on both name and rating, this query will assign the same rating to both of them.

---

Tables are more formally known as relations in database literature - they are not guaranteed to be ordered (they are sets of "tuples"), so your question doesn't make sense. If you need to rely on an order/position, you need to define an additional column (like an auto-incrementing ID column) to capture and store that info.

---

Is this any help > http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/ ?

---

Would offset not work like so?

SELECT * FROM Table ORDER BY rating DESC LIMIT 1,6

This would return 1 row that has been off setted by 6 rows ? or am I mistaken, the syntax would be

SELECT * FROM Table ORDER BY rating DESC LIMIT 1 , {{POS}}