Contradiction between TOP and ORDER BY in a ranking query

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

Count me out of replying!

TC

"Irene" <italianculture@hotmail.com> wrote in message
news:cc6fc085.0310010224.7b40155a@posting.google.c om...[color=blue]
> 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[/color]

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)
"TC" <a@b.c.d> wrote in message news:1065004691.679397@teuthos...[color=blue]
> Count me out of replying!
>
> TC
>
>
> "Irene" <italianculture@hotmail.com> wrote in message
> news:cc6fc085.0310010224.7b40155a@posting.google.c om...[color=green]
> > 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[/color]
>
>[/color]

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)
"Irene" <italianculture@hotmail.com> wrote in message
news:cc6fc085.0310010224.7b40155a@posting.google.c om...[color=blue]
> 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[/color]

"John Viescas" <JohnV@nomail.please> wrote in news:#m9408BiDHA.1688
@TK2MSFTNGP10.phx.gbl:
[color=blue]
> 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.[/color]

Count me out of replying!

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

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 <JohnV@nomail.please> wrote in message
news:#m9408BiDHA.1688@TK2MSFTNGP10.phx.gbl...[color=blue]
> 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[/color]
already[color=blue]
> posted an answer.[/color]

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)
"TC" <a@b.c.d> wrote in message news:1065086338.327113@teuthos...[color=blue]
> 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[/color]
some[color=blue]
> information, but ask me a question, & I do not bother to answer them, then[/color]
I[color=blue]
> repost my question a few days later, the person who tried to help me[/color]
before,[color=blue]
> might not bother to try again!"
>
> TC
>
>
> John Viescas <JohnV@nomail.please> wrote in message
> news:#m9408BiDHA.1688@TK2MSFTNGP10.phx.gbl...[color=green]
> > Please do not reply to a message that you do not intend to answer.[/color][/color]
Folks[color=blue][color=green]
> > scanning the newsgroups to help others will assume that someone has[/color]
> already[color=green]
> > posted an answer.[/color]
>
>
>[/color]

"John Viescas" <JohnV@nomail.please> wrote in message news:<e5Rwn8BiDHA.2748@TK2MSFTNGP11.phx.gbl>...[color=blue]
> 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
>[/color]

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

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

Regards,
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

[color=blue]
> 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
>[/color]

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

Irene <italianculture@hotmail.com> wrote in message
news:cc6fc085.0310022256.7f0dbf11@posting.google.c om...[color=blue]
> 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
>
>[color=green]
> > 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[/color][/color]
some[color=blue][color=green]
> > information, but ask me a question, & I do not bother to answer them,[/color][/color]
then I[color=blue][color=green]
> > repost my question a few days later, the person who tried to help me[/color][/color]
before,[color=blue][color=green]
> > might not bother to try again!"
> >
> > TC
> >[/color][/color]

"John Viescas" <JohnV@nomail.please> wrote in message
news:e5Rwn8BiDHA.2748@TK2MSFTNGP11.phx.gbl...[color=blue]
> 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)[/color]

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?

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)
"Steve Gerrard" <notstevegerrard@comcast.net> wrote in message
news:0FqdnRnCTvevyOOiU-KYjA@comcast.com...[color=blue]
>
> "John Viescas" <JohnV@nomail.please> wrote in message
> news:e5Rwn8BiDHA.2748@TK2MSFTNGP11.phx.gbl...[color=green]
> > 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)[/color]
>
> Along with Irene, I appreciate your answer. I have known about sub[/color]
queries, but[color=blue]
> I never seem to get the syntax correct. Your example will help.
>
> Now my question. What is the difference, in the example above, between[/color]
writing[color=blue]
> the last query as you have, with the "IN (SELECT Score..." sub query, and[/color]
a[color=blue]
> similar one, in which you instead INNER JOINed with qryTopScores on[/color]
Scores.Score[color=blue]
> = qryTopScores.Score? Wouldn't that do the same thing?
>
>
>[/color]

"Steve Gerrard" <notstevegerrard@comcast.net> wrote in message news:<0FqdnRnCTvevyOOiU-KYjA@comcast.com>...[color=blue]
> "John Viescas" <JohnV@nomail.please> wrote in message
> news:e5Rwn8BiDHA.2748@TK2MSFTNGP11.phx.gbl...[color=green]
>>[/color]
> 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?[/color]

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