Databases
StackOverflow2013
Postsdbo
POHow can I calculate rank based on highest points and lowest time in SQL server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I calculate rank based on highest points and lowest time in SQL server
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndriy M
    1. USSilicaGel
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow can I calculate rank based on highest points and lowest time in SQL server
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow can I calculate rank based on highest points and lowest time in SQL server
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow can I calculate rank based on highest points and lowest time in SQL server
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think this clause would do the trick for you Order By Credits DESC, timePlayed ASC
      singulars
      1. POHow can I calculate rank based on highest points and lowest time in SQL server
      1. USStefan H
    2. CO@StefanH - Agreed - OLAP functions don't need aggregate functions. Other than that, don't use implicit-join syntax (comma-separated `FROM` clause, always explicitly state your joins. It's not so bad here, but can become cumbersome later. Also, what happens if more than 5 players have the same rank? You probably want to use the following clause instead/in addition to: `HAVING rank < 6`, which gets the top 5 ranks of players (minimum of 5 players, no maximum).
      singulars
      1. POHow can I calculate rank based on highest points and lowest time in SQL server
      1. USClockwork-Muse
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload