SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru gavwvin's Avatar
    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)

  2. #2
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    where pos = the position required.

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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 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
    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
    Code:
    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!"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru gavwvin's Avatar
    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).

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 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 too
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •