SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: Finding position
-
Feb 4, 2005, 12:54 #1
- Join Date
- Nov 2004
- Location
- Cornwall, UK
- Posts
- 686
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Finding position
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)
-
Feb 4, 2005, 17:22 #2
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
Code:SELECT * FROM table ORDER BY points DESC LIMIT pos, 1
Also, I would only select the actual colums you require, rather that a SELECT *
-
Feb 4, 2005, 19:14 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
totally agree on not using SELECT *
however, i think the question was how to calculate the position for a given id:Code:select foo , bar , something as position from thetable where id = 937
because the something in this case is simply a count of all the ids that have a higher points valueCode:select foo , bar , ( select count(*) from thetable where points >= t.points) as position from thetable as t where id = 937
-
Feb 6, 2005, 03:08 #4
- Join Date
- Nov 2004
- Location
- Cornwall, UK
- Posts
- 686
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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).
-
Feb 6, 2005, 07:38 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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 tooCode: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
-
Feb 7, 2005, 03:20 #6
- Join Date
- Nov 2004
- Location
- Cornwall, UK
- Posts
- 686
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks!
Bookmarks