Contradiction between TOP and ORDER BY in a ranking query

[TC]

Count me out of replying!

TC

 

[Irene]

Hi all again,

Well, I have my Athletics database with Athletes, Competitions, Scores
tables.

I have a ranking query where I get back the list of the
competitions-athletes and scores opportunely sorted.

I also want to be able to:
1) Select just the top <n> best scores [= SELECT TOP <n>]
2) View the multiple equal-score in order of date in which the
competition occurred [=ORDER BY Score, CompetitionDate]
3) Selecting a TOP <n> limit, view more than <n> best scores if the
(n+1)th, (n+2)th,..(n+m)th extra records have the same score than the
<n>th.

Now, actually TOP allows me view more than <n> athletes in case [3]
occurs, but this works just if I ORDER BY Score only, if I order by
CompetitionDate too, extra records do not appear.

So, at the moment, I am able to return a query that meets requirements
[1] And [2]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes … INNER JOIN Competitions
ORDER BY Score,CompetitionDate

…or one that meets req. [1] and [3]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes … INNER JOIN Competitions
ORDER BY Score

…or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes … INNER JOIN Competitions
ORDER BY Score, CompetitionDate

Is there a way to build a query that allows me to meet requirements
[1][2][3] contemporarly ?!?

Thanks a lot.

Regards,
Irene

 

[John Viescas]

Irene-

So, you want the TOP <n> scores, and then list all the records that have
that score, right? First, find the distinct scores:

qryDistinctScore:
SELECT DISTINCT Score FROM Scores

Now, find the top <n>:

qryTopScores:
SELECT TOP <n> Score
FROM qryDistinctScore
ORDER BY Score Desc

Now, solve your problem:

SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
WHERE Score IN
(SELECT Score FROM qryTopScores)
ORDER BY Score, CompetitionDate

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

 

[John Viescas]

Please do not reply to a message that you do not intend to answer. Folks
scanning the newsgroups to help others will assume that someone has already
posted an answer.

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

Count me out of replying!

TC

Hi all again,

Well, I have my Athletics database with Athletes, Competitions, Scores
tables.

I have a ranking query where I get back the list of the
competitions-athletes and scores opportunely sorted.

I also want to be able to:
1) Select just the top <n> best scores [= SELECT TOP <n>]
2) View the multiple equal-score in order of date in which the
competition occurred [=ORDER BY Score, CompetitionDate]
3) Selecting a TOP <n> limit, view more than <n> best scores if the
(n+1)th, (n+2)th,..(n+m)th extra records have the same score than the
<n>th.

Now, actually TOP allows me view more than <n> athletes in case [3]
occurs, but this works just if I ORDER BY Score only, if I order by
CompetitionDate too, extra records do not appear.

So, at the moment, I am able to return a query that meets requirements
[1] And [2]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score,CompetitionDate

.or one that meets req. [1] and [3]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score

.or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score, CompetitionDate

Is there a way to build a query that allows me to meet requirements
[1][2][3] contemporarly ?!?

Thanks a lot.

Regards,
Irene

 

[Brian Camire]

You might use three queries. The first (say, named "Query1") would return
the distinct Score values from the "Scores" table. The SQL might look
something like this:

SELECT DISTINCT
Scores.Score
FROM
Scores

The second (say, named "Query1") would return the TOP <n> distinct Score
values from "Query1". The SQL might look something like this for n=10

SELECT TOP 10
Query1.*
FROM
Query1
ORDER BY
Query1.Score DESC

The third query would join the "Athletes", "Competitions" and "Scores"
tables along with "Query1" to select only the records with top 10 disinct
scores and order them by Score and CompetionDate. The SQL might look
something like this:

SELECT
Athletes.AthleteName,
Competions.CompetionDate,
Competions.CompetionPlace,
Scores.Score
FROM
((Athletes
INNER JOIN
Scores
ON
Athletes.AthleteID = Scores.AthleteID)
INNER JOIN
Competions
ON
Scores.CompetionID = Competions.CompetionID)
INNER JOIN
Query2
ON
Scores.Score = Query2.Score
ORDER BY
Scores.Score DESC ,
Competions.CompetionDate

 

[Lyle Fairfield]

Please do not reply to a message that you do not intend to answer. Folks
scanning the newsgroups to help others will assume that someone has already
posted an answer.

Count me out of replying!

 

[TC]

Point taken.

I guess I was really trying to get the OP to think this:

"Gee, if someone tries to help me in a previous thread, & they give me some
information, but ask me a question, & I do not bother to answer them, then I
repost my question a few days later, the person who tried to help me before,
might not bother to try again!"

TC

 

[John Viescas]

Ah. I didn't see the previous exchange. It might have been a good idea to
refer the OP to your original reply and ask what it was they didn't
understand.

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

 

[Irene]

Irene-

Now, solve your problem:

SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
WHERE Score IN
(SELECT Score FROM qryTopScores)
ORDER BY Score, CompetitionDate

Thanks for your help John. This definitely solve my problem.

I still need to be familiar with subqueries....

Regards,
Irene

 

[Irene]

Hi TC,

I see you have a problem with me.

Actually, I did reply you in the other thread. This was the message:

----
Hi TC,

Someonelse gave me this address:
http://www.mvps.org/access/queries/qry0020.htm

It looks like I have a solution for my problem.

However I have still a couple of problems, complications due to the
major complexity of my database in comparison to the Library database
(see the linked page).

But I will open an new post for these as soon as I can manage to
phrase
them correctly.

Thanks for your time.
Irene
----

I don't know why your reply didn't go on the newsserver. I'm using a
web interfaced newsreader. Maybe it is not so reliable or maybe my
post has been deleted for some reasons that I don't know.

See also my replies to Steve in comp.lang.basic.visual.database and to
Michel in microsoft.public.access.queries on the same subject [Best
athletes...].
Why didn't I crossposted? Because mailgate.org does not manage
"microsoft.public" groups!

I will use Google from now on, even if it takes so long to see your
message (and the possible replies). Or I will look for some others.
Cannot use newsreaders from here.

In any case, sorry if this has caused misunderstandings, it was not
intentional.

Regards,
Irene

 

[TC]

Ok. I apologise!

That message did not appear on my server. This has happened before, so I
should have thought of that.

Sorry to go off half-cocked,

TC

Hi TC,

I see you have a problem with me.

Actually, I did reply you in the other thread. This was the message:

----
Hi TC,

Someonelse gave me this address:
http://www.mvps.org/access/queries/qry0020.htm

It looks like I have a solution for my problem.

However I have still a couple of problems, complications due to the
major complexity of my database in comparison to the Library database
(see the linked page).

But I will open an new post for these as soon as I can manage to
phrase
them correctly.

Thanks for your time.
Irene
----

I don't know why your reply didn't go on the newsserver. I'm using a
web interfaced newsreader. Maybe it is not so reliable or maybe my
post has been deleted for some reasons that I don't know.

See also my replies to Steve in comp.lang.basic.visual.database and to
Michel in microsoft.public.access.queries on the same subject [Best
athletes...].
Why didn't I crossposted? Because mailgate.org does not manage
"microsoft.public" groups!

I will use Google from now on, even if it takes so long to see your
message (and the possible replies). Or I will look for some others.
Cannot use newsreaders from here.

In any case, sorry if this has caused misunderstandings, it was not
intentional.

Regards,
Irene

Point taken.

I guess I was really trying to get the OP to think this:

"Gee, if someone tries to help me in a previous thread, & they give me some
information, but ask me a question, & I do not bother to answer them, then I
repost my question a few days later, the person who tried to help me before,
might not bother to try again!"

TC

 

[Steve Gerrard]

Irene-

So, you want the TOP <n> scores, and then list all the records that have
that score, right? First, find the distinct scores:

qryDistinctScore:
SELECT DISTINCT Score FROM Scores

Now, find the top <n>:

qryTopScores:
SELECT TOP <n> Score
FROM qryDistinctScore
ORDER BY Score Desc

Now, solve your problem:

SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
WHERE Score IN
(SELECT Score FROM qryTopScores)
ORDER BY Score, CompetitionDate

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

Along with Irene, I appreciate your answer. I have known about sub queries, but
I never seem to get the syntax correct. Your example will help.

Now my question. What is the difference, in the example above, between writing
the last query as you have, with the "IN (SELECT Score..." sub query, and a
similar one, in which you instead INNER JOINed with qryTopScores on Scores.Score
= qryTopScores.Score? Wouldn't that do the same thing?

 

[John Viescas]

Steve-

My version is likely to still be updatable. Adding the query in the FROM
clause will cause the query to be not updatable any more because the query
contains a DISTINCT clause.

--
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)

 

[Irene]

Now my question. What is the difference, in the example above, between writing
the last query as you have, with the "IN (SELECT Score..." sub query, and a
similar one, in which you instead INNER JOINed with qryTopScores on Scores.Score
= qryTopScores.Score? Wouldn't that do the same thing?

Maybe I can answer here: the example above with the subquery is much
slower than the INNER JOINed one (I have a database with 10000
competitions).

I red that the INNER JOINed one works only with Jet 4 (other diff.).

I also still need to be familiar with the subqueries and tips and
tricks.
Irene