Finding position

[gavwvin]

Is there a way to find out with sql only where an certain id appears in a query's results? For instance I have a query that gives me a table of current league standings (SELECT * FROM table ORDER BY points DESC). I want to only select one row from that and find out what position they are- so I can SELECT * FROM table WHERE id=".$id." ORDER BY points DESC- but how do I find out what position they are in in the results? (I know I could do this with php by selecting them all then looping through to find what position they're in, but I was just wondering if there's an more efficient way to do it with SQL)

[swdev]

If I understand you correctly, you want to select the nth position in the table, ordered by points?

It this is the case, then use

 SELECT
   *
 FROM
   table
 ORDER BY
   points DESC
 LIMIT pos, 1

where pos = the position required.

Also, I would only select the actual colums you require, rather that a SELECT *

[r937]

totally agree on not using SELECT *

however, i think the question was how to calculate the position for a given id:

select foo
     , bar
     , something as position
  from thetable 
 where id = 937

i think we should have a sticky that says something like "if you're not on 4.1 yet, you should say so"

because the something in this case is simply a count of all the ids that have a higher points value

select foo
     , bar
     , ( select count(*)
           from thetable
          where points >= t.points) as position
  from thetable as t
 where id = 937

having said that, i know just what's gonna happen here, gavin's gonna come back with "but i'm not on 4.1 yet!"

[gavwvin]

thanks r937 thats exactly what I wanted, but yes, my testing server is 4.1 but the server it will be on eventually isn't- sorry, you're right I should have said that at the start, I didn't think.
Is there a way without 4.1? If not I'll just have to select all and loop through with php to find it (which isn't really ideal but oh well).

[r937]

looping through the entire sorted league standings to find the position of the specified team isn't a bad solution, the query is dead simple and the bandwidth to extract the entire league can't be all that bad (unless there are millions of teams)

you may have heard that subqueries can often be accomplished with joins, and that's the case here too

select t1.foo
     , t1.bar
     , count(*) as position
  from thetable as t1
inner
  join thetable as t2
    on t1.points <= t2.points
 where t1.id = 937

[gavwvin]

Thanks!