Mysql League Positions

Peter B

I'm creating a fantasy league website. I need to display the position of members when they log in.

In my overall table I get all members and order them by the amount of points they have. Then in my php code I print their position as the order in which they appear. i.e. Row 0 = 1st position, Row 1 = 2nd Position etc

When I want to just display a single persons position I do not want to have to call the same query as my overall table, return the whole resultset and go through that until I reach the member id which I can then say their position is row + 1.


Is there a mysql function query where I can find where a row ranks in a table?

Eoin

Unfortunately, mySQL doesn't support rownum, otherwise you could have done this:

SELECT 
  myCol
FROM 
  (select rownum as MyCol,
  UserID from myLeagueTable)
WHERE 
  userID = 'TheUserID'

but if you google for mySQL & rownum you may be able to get a workaround.

This will return the number of the row, but I don't know how to nest this as I have done above:

SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;

amen

not sure how to do it on MySql
but i think you are missing something on your website
most fantasy football sites let you see your position for each week
so you prob need another table with the week number, team , team score and team position

of course that way when you want an idividual team position is very easy