problem with SQL statement in high score database

Question

I have created a letter game in java and i need to include a high score function into it. Using Microsoft access database through JDBC. The table in the database contains the following

Table name Easy

fields
Name
Score 
Time

In need an SQL statement that displays the top 5 scores in the order highest score and lowest time. eg. if there are two players who scored 20 the player with the smaller time should be on top but if there is a player who scored 21, he should be on top regardless of the time.

Answer 1

select top 5 
    name,
    score,
    time

from easy

order by score desc, time asc

Answer 2

Select top 5 Name
From Easy
order by score desc, time asc

Answer 3

SELECT TOP 5 Score, Time
FROM Easy
ORDER BY Score DESC, Time

Answer 4

Other folks have suggested the SELECT TOP 5... solutions, but fwiw, this syntax is nonstandard SQL supported by Microsoft SQL Server and Sybase. If you use another brand of database, it won't work. You should be edit your question and specify what brand and version of database you're using, because the answer could be different based on that information.

Answer 5

select top5 Score, Time from Easy order byScore desc, Time

Answer 6

Not that you asked, but you might be interested to know that the answer is different with different databases. For instance, in Oracle that would be

select
    name,
    score,
    time
from easy
where rownum <= 5
order by score desc, time asc

while in PostgreSQL, it would be

select
    name,
    score,
    time
from easy
order by score desc, time asc
limit 5

Answer 7

The table is named Easy, which seems to indicate that it's for players who play on Easy mode. I'd recommend adding a column for difficulting and just having one big HighScores table.

SELECT TOP 5 
  name, score, time
FROM HighScores
WHERE difficulty = 'Easy'
ORDER BY score desc, time asc