title: How can I calculate rank based on highest points and lowest time in SQL server
body: <p>I have searched and searched but have been unable to get this working.  I'd really appreciate it if someone could help me out.</p>
  
  <p>I have a table that stores the information for games played (small quiz application.)
  I need to be able to determine the top 5 ranked players based on having the highest score, followed by the lowest time.  (Many players have the max score).
  I also need to determine the users rank at any given time.</p>
  
  <p>Here is a sample of the games table:</p>
  
  <pre>
  user_id  credits  time_played  alias
  -------  -------  -----------  --------------
  64       490      180          DaPlaya
  93       690      187          Superman
  64       336      187          DaPlaya
  75       196      192          Flake
  93       182      197          Superman
  57       844      198          John Smith
  75       350      198          Flake
  64       858      384          DaPlaya
  73       858      400          littleguy
  57       858      412          John Smith
  101      858      420          FLASH
  73       858      423          littleguy
  73       858      434          littleguy
  65       858      460          Sheena
  122      858      540          Sugar Queen
  126      858      545          Rachel
  176      350      2417         The Fire Light
  157      350      2442         Big Q
  161      350      2456         Joey Blue
  </pre>
  
  <p>The current query i'm using is this:</p>
  
  <pre><code>select top 5 alias,user_id, 
     rank() over (order by max(credits) desc,min(time_played)) as rank 
  from games,users 
  where games.user_id=users.id and users.id &lt;&gt; 20 and games.credits &gt; 0 
  group by user_id,alias
  </code></pre>
  
  <p>(As you can see from the query, the alias is actually stored in the users table.)</p>
  
  <p>I thought this query was working, as it seemed to show the correct results, however now that the site is live, and I have thousands of games, I can see that it's wrong.</p>
  
  <p>The above query basically gives me 5 players that have the highest possible score, but it ranks them on their lowest game-time.  (so faster games with lower scores).</p>
  
  <p>Please let me know how I can tweak/modify this query or rewrite it entirely.</p>
  
  <p>(Note, I could do this much simpler using php with a loop, but that wouldn't be very efficient.)</p>
  
  <p>I'm happy to provide more information if needed.</p>
  
  <p>Thanks in advance,
  Aaron.</p>