Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    9

    Unanswered: get the record position in query result

    Hi, first take a look to this simple table

    ID,Name,Points
    1,A.Smith,18
    2,M.James,19
    3,S.Fernandez,17
    4,A.Sweet,23

    i need a query to sort the table by Points Column and give me the rank of ID=3 that in this example should be 4.

  2. #2
    Join Date
    Aug 2005
    Posts
    30

    Get serial number in your mysql query

    SET @a=0;
    SELECT (@a:=@a+1) as Rank, Name, Points, ID FROM `MyTableName` order by Points desc;

  3. #3
    Join Date
    Jun 2009
    Posts
    9
    Hi, this query returns all records with record number ! but i just want to have record Id = 3 with it's rank !

    suppose i have 20000 records and should loop thru it to find the rank of id = 3 ? or use filters ? , i don't want to load all the data !!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.ID
         , t.Name
         , t.Points
         , ( SELECT COUNT(*) + 1
               FROM daTable
              WHERE Points > t.Points ) AS rank
      FROM daTable AS t
     WHERE t.ID = 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    9
    yeah , it works. Nice Idea !

  6. #6
    Join Date
    Jun 2009
    Posts
    9
    r937, it was a very nice trick but there is something that coulnt make it !

    maybe we have such table :
    ID,Name,Points
    1,A.Smith,18
    2,M.James,18
    3,S.Fernandez,17


    if we query for id=3 , we will get 3 but S.Fernandez is the second top point.
    what do you think about this issue ? or any idea friends ?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you describe is the difference between rank and dense rank

    S.Fernandez is the second top point, but the third person in point rank

    try replacing COUNT(*) with COUNT(DISTINCT Points) in the subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2012
    Posts
    44
    I searched and found this fantastic query:

    Quote Originally Posted by r937 View Post
    Code:
    SELECT t.ID
         , t.Name
         , t.Points
         , ( SELECT COUNT(*) + 1
               FROM daTable
              WHERE Points > t.Points ) AS rank
      FROM daTable AS t
     WHERE t.ID = 3
    Now, if I would like the sum of points instead, how could I do then?

    I tried this,

    Code:
    SELECT t.ID
         , t.Name
         , SUM(t.Points) AS sumPoints
         , ( SELECT COUNT(*) + 1
               FROM daTable
              WHERE Points > t.sumPoints) AS rank
      FROM daTable AS t
     WHERE t.ID = 3
    GROUP BY t.ID
    but then I get the "Unknown column 't.sumPoints' in 'where clause'".

    Thanks.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    I searched and found this fantastic query:
    oh, you are too kind

    Quote Originally Posted by Haydn View Post
    Now, if I would like the sum of points instead, how could I do then?
    i need to understand what you're asking for

    could you please give some comprehensive sample data, and show the results that you want from that data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    oh, you are too kind

    i need to understand what you're asking for

    could you please give some comprehensive sample data, and show the results that you want from that data
    Ok, if I take the authors example, but instead of ID being unique, it could be multiple records for each ID (I skipped the name here).

    ID,Points
    1, 5
    2, 8
    3, 9
    2, 5
    1, 6
    4, 8
    3, 1

    So he sum of the different ID's would mean that ID 2 is ranked #1 (13 points), ID 1 is ranked #2 (11 points), ID 3 is ranked #3 (10 points) and ID 4 is ranked #4 (8 points).

    The result I want is the rank for e.g. ID 3, in the above case 3.

    I hope that is understandable, thanks.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    I hope that is understandable, thanks.
    yup, it is now

    all we have to do is take the previous solution...
    Code:
    SELECT t.ID
         , t.Points
         , ( SELECT COUNT(*) + 1
               FROM daTable
              WHERE Points > t.Points ) AS rank
      FROM daTable AS t
     WHERE t.ID = 3
    and instead of using the actual table (daTable), we use a subquery instead...
    Code:
    SELECT t.ID
         , t.sumpoints
         , ( SELECT COUNT(*) + 1
               FROM ( SELECT id
                           , SUM(points) AS sumpoints
                        FROM daTable 
                      GROUP
                          BY id ) AS t2
              WHERE sumpoints > t.sumpoints ) AS rank
      FROM ( SELECT id
                  , SUM(points) AS sumpoints
               FROM daTable 
             GROUP
                 BY id ) AS t
     WHERE t.ID = 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    yup, it is now

    all we have to do is take the previous solution...
    Code:
    SELECT t.ID
         , t.Points
         , ( SELECT COUNT(*) + 1
               FROM daTable
              WHERE Points > t.Points ) AS rank
      FROM daTable AS t
     WHERE t.ID = 3
    and instead of using the actual table (daTable), we use a subquery instead...
    Code:
    SELECT t.ID
         , t.sumpoints
         , ( SELECT COUNT(*) + 1
               FROM ( SELECT id
                           , SUM(points) AS sumpoints
                        FROM daTable 
                      GROUP
                          BY id ) AS t2
              WHERE sumpoints > t.sumpoints ) AS rank
      FROM ( SELECT id
                  , SUM(points) AS sumpoints
               FROM daTable 
             GROUP
                 BY id ) AS t
     WHERE t.ID = 3
    How good are you!? Works perfect, thanks a lot!

    I admit I don't fully grasp this query though. I'll spend more with it. I might need to JOIN in this later as well, but I'll get back here if I run into problems.

    Thanks again.

Posting Permissions

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