Contradiction between TOP and ORDER BY in a ranking query

[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 , CompetitionPlac e, Score
Scores INNER JOIN Athletes … INNER JOIN Competitions
ORDER BY Score,Competiti onDate

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

…or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate , CompetitionPlac e, 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

[TC]

Count me out of replying!

TC
"Irene" <it************ @hotmail.com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...

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 , CompetitionPlac e, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score,Competiti onDate

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

.or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate , CompetitionPlac e, 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]

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

Count me out of replying!

TC
"Irene" <it************ @hotmail.com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...

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 , CompetitionPlac e, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score,Competiti onDate

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

.or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate , CompetitionPlac e, 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:

qryDistinctScor e:
SELECT DISTINCT Score FROM Scores

Now, find the top <n>:

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

Now, solve your problem:

SELECT AtheteName, CompetitionDate , CompetitionPlac e, 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" <it************ @hotmail.com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...

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 , CompetitionPlac e, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score,Competiti onDate

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

.or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate , CompetitionPlac e, 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

[Lyle Fairfield]

"John Viescas" <Jo***@nomail.p lease> wrote in news:#m9408BiDH A.1688
@TK2MSFTNGP10.p hx.gbl:

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!

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

[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 <Jo***@nomail.p lease> wrote in message
news:#m******** ******@TK2MSFTN GP10.phx.gbl...

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]

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

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 <Jo***@nomail.p lease> wrote in message
news:#m******** ******@TK2MSFTN GP10.phx.gbl...

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.

[Irene]

"John Viescas" <Jo***@nomail.p lease> wrote in message news:<e5******* *******@TK2MSFT NGP11.phx.gbl>. ..

Irene-

Now, solve your problem:

SELECT AtheteName, CompetitionDate , CompetitionPlac e, 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.databas e and to
Michel in microsoft.publi c.access.querie s on the same subject [Best
athletes...].
Why didn't I crossposted? Because mailgate.org does not manage
"microsoft.publ ic" 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 misunderstandin gs, 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

[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

Irene <it************ @hotmail.com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...

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.databas e and to
Michel in microsoft.publi c.access.querie s on the same subject [Best
athletes...].
Why didn't I crossposted? Because mailgate.org does not manage
"microsoft.publ ic" 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 misunderstandin gs, 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