Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012

    Question Unanswered: Sorting, Identify position, without returning all results

    So lets say I want to find a Rank for a user compared to all other users for a specific variable.

    So I can do something like
    Select * FROM users order by revenuegenerated

    If I want to see where Fred ranks against his peers, I would return all these results, loop through them until I find Fred and using a counter I would get my answer.

    Is there a more query friendly way to do this? My database tables are pretty large, and I see no reason to return 700K results when I simply need to know where one user falls in line.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT COUNT(*) + 1 AS rank
      FROM users AS t
      JOIN users AS x
        ON x.revenuegenerated > t.revenuegenerated 
     WHERE t.username = 'Fred' | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Makes perfect sense. Makes me want to knock myself in the head, I should have figured that one out. Thanks for your help and have a good day,

Posting Permissions

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