Q: How can I calculate rank based on highest points and lowest time in SQL server I have searched and searched but have been unable to get this working.  I'd really appreciate it if someone could help me out.
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.
Here is a sample of the games table:

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

The current query i'm using is this:
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 <> 20 and games.credits > 0 
group by user_id,alias

(As you can see from the query, the alias is actually stored in the users table.)
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.
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).
Please let me know how I can tweak/modify this query or rewrite it entirely.
(Note, I could do this much simpler using php with a loop, but that wouldn't be very efficient.)
I'm happy to provide more information if needed.
Thanks in advance,
Aaron.

A: with user_ranked_games as (
  select user_id,
         credits,
         time_played,
         row_number() over (  partition by user_id 
                              order by credits desc, time_played asc
                           ) as game_rank
    from games
)
select top 5
       rank() over (order by g.credits desc, g.time_played asc) as rank,
       g.user_id,
       u.alias,
       g.credits,
       g.time_played
  from user_ranked_games g
  join users u
    on g.user_id = u.user_id
 where g.game_rank=1
order by rank

I successfully tested the above on SQL Fiddle. I modified the data values so that littleguy has 2 of the top 5 scores. But my query above correctly lists littleguy only once in the ranked top 5 players. 
Note - My original sql fiddle had duplicate user rows (I didn't bother defining PKs), which led to different outputs depending on where the join was done. I've since fixed the data and removed the join from the CTE and put it in the outer-most query as I originally intended.
Update in response to comment
A small modification provides a query that can look up the user rank and scores for an individual user:
with user_ranked_games as (
  select user_id,
         credits,
         time_played,
         row_number() over (  partition by user_id 
                              order by credits desc, time_played asc
                           ) as game_rank
    from games
),
ranked_users as (
  select rank() over (order by g.credits desc, g.time_played asc) as rank,
         g.user_id,
         u.alias,
         g.credits,
         g.time_played
    from user_ranked_games g
    join users u
      on g.user_id = u.user_id
   where g.game_rank=1
)
select * from ranked_users where user_id=93

You might want to consider creating a view that can be conveniently used to answer both questions:
create view ranked_users as
with user_ranked_games as (
  select user_id,
         credits,
         time_played,
         row_number() over (  partition by user_id
                              order by credits desc, time_played asc
                           ) as game_rank
    from games
),
select rank() over (order by g.credits desc, g.time_played asc) as rank,
       g.user_id,
       u.alias,
       g.credits,
       g.time_played
  from user_ranked_games g
  join users u
    on g.user_id = u.user_id
 where g.game_rank=1

The first query then becomes:
select top 5 * from ranked_users order by rank

And the 2nd query becomes
select * from ranked_users where user_id=93


A: If you want the rank of your players with the highest score, followed by the lowest time then use this:
select 
    topalias,
    user_id, 
    rank() OVER (ORDER BY Credits DESC, timePlayed ASC) AS Rank
from 
    games
    INNER JOIN users 
        on games.user_id = users.id 
 Order By 
     Credits DESC, timePlayed ASC


A: I admit to be slightly confused by your SELECT clause, but... I think I'd just put "ORDER BY credits DESC, time_played ASC" after the GROUP BY clause. And possibly just SELECT *.
If you want a solution that guarantees 5 different names, change the FROM clause to get a table that contains distinct names from that table.

A: The complicating factor is (if I understand correctly) that you only want to show each player once, even if he or she has (for example) the five best scores.  So you must first rank all games, then get the best game for each player, and then get the details for the top five of these.
CREATE TABLE #Games
    (
    GameID  INT NOT NULL PRIMARY KEY,
    UserID  INT NOT NULL,
    Rank    INT NOT NULL,
    )
INSERT INTO #Games (GameID, UserID, Rank)
    SELECT
        game_id, user_id, ROW_NUMBER() OVER (ORDER BY credits DESC, time_played) AS Rank
    FROM
        games

SELECT TOP 5
    U2.alias, G2.credits, G2.time_played
FROM
    (
    SELECT UserID, MIN(Rank) AS BestRank
    FROM #Games
    GROUP BY UserID
    ) AS U1
    INNER JOIN users AS U2 ON U1.UserID = U2.user_id
    INNER JOIN #Games AS G1 ON U.BestRank = G1.Rank
    INNER JOIN games AS G2 ON G1.GameID = G2.game_id
ORDER BY
    U1.BestRank

