TOP doesn't work with ORDER BY correctly, please help

[James]

Here are my 2 queries

SELECT TOP 20 accounts.Username as Username,
eb.ScoreTotal as ScoreTotal, eb.SecPlayed as SecPlayed,
eb.LevelNum as LevelNum FROM accounts INNER JOIN eb ON
accounts.Seed = eb.Seed ORDER BY eb.ScoreTotal DESC;

SELECT TOP 20 accounts.Username as Username,
eb.ScoreTotal as ScoreTotal, eb.SecPlayed as SecPlayed,
eb.LevelNum as LevelNum FROM accounts INNER JOIN eb ON
accounts.Seed = eb.Seed ORDER BY eb.SecPlayed ASC;

the problem is the first query works as expected, getting
the top 20 scores in descending order, but the second
query gets all scores as if ignoring the top 20 command?
when i change the second query to DESC it works as
normal, what the heck is going on here, do I need parens
somewhere where I don't have them? Thanks for your
guidance.

PS, this is for the stats page for a game I am almost
done writing

 

[John Viescas]

How many rows are you getting from the second query? Note that when you
have duplicate ScoreTotal values, you can get more than 20 rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[James]

Hundreds, yes that is what is causing, all of the
scoretotals are the same, so how can I do what I want,
maybe by using DISTINCT?

-----Original Message-----
How many rows are you getting from the second query? Note that when you
have duplicate ScoreTotal values, you can get more than

20 rows.

 

[John Viescas]

If you want only 20, and hundreds of bottom sections are all zero (or the
same value), then you could try:

SELECT TOP 20 accounts.Username as Username,
eb.ScoreTotal as ScoreTotal, eb.SecPlayed as SecPlayed,
eb.LevelNum as LevelNum
FROM accounts INNER JOIN eb ON
accounts.Seed = eb.Seed
ORDER BY eb.SecPlayed ASC, Rnd([LevelNum])

... which should give you a random 20 records.

Note that in your first query you sorted by ScoreTotal. Why SecPlayed in
this query?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[James]

awesome john, thanks. i tried using DISTINCT but that
doesn't really fit my goal because even though scores and
time may be tied it should still show duplicates. but
your post gave me a big DUH moment, just sort other
columns next, then lastly with a random number based on
the id. BTW the second query is sorted on secplayed
because it is a best times table, the first table is a
best score table, here is my end query which is working
great now

SELECT TOP 20 accounts.Username as Username,
eb.ScoreTotal as ScoreTotal, eb.SecPlayed as SecPlayed,
eb.LevelNum as LevelNum FROM accounts,eb WHERE
accounts.Seed = eb.Seed ORDER BY eb.SecPlayed ASC,
eb.ScoreTotal DESC, eb.LevelNum ASC, Rnd([eb.Id]);

also is it better to do an INNER JOIN on the data or this
form of a UNION using an Access 2000 mdb? is either
faster? thanks again for your help.