By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,493 Members | 3,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,493 IT Pros & Developers. It's quick & easy.

Contradiction between TOP and ORDER BY in a ranking query

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
TC
Count me out of replying!

TC
"Irene" <it************@hotmail.com> wrote in message
news:cc**************************@posting.google.c om...
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

Nov 12 '05 #2

P: n/a
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.google.c om...
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


Nov 12 '05 #3

P: n/a
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" <it************@hotmail.com> wrote in message
news:cc**************************@posting.google.c om...
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

Nov 12 '05 #4

P: n/a
"John Viescas" <Jo***@nomail.please> wrote in news:#m9408BiDHA.1688
@TK2MSFTNGP10.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.


Count me out of replying!

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #5

P: n/a
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.please> wrote in message
news:#m**************@TK2MSFTNGP10.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.


Nov 12 '05 #6

P: n/a
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.please> wrote in message
news:#m**************@TK2MSFTNGP10.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.


Nov 12 '05 #7

P: n/a
"John Viescas" <Jo***@nomail.please> wrote in message news:<e5**************@TK2MSFTNGP11.phx.gbl>...
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
Nov 12 '05 #8

P: n/a
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

Nov 12 '05 #9

P: n/a
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.google.c om...
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

Nov 12 '05 #10

P: n/a

"John Viescas" <Jo***@nomail.please> wrote in message
news:e5**************@TK2MSFTNGP11.phx.gbl...
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?

Nov 12 '05 #11

P: n/a
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" <no*************@comcast.net> wrote in message
news:0F********************@comcast.com...

"John Viescas" <Jo***@nomail.please> wrote in message
news:e5**************@TK2MSFTNGP11.phx.gbl...
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?

Nov 12 '05 #12

P: n/a
"Steve Gerrard" <no*************@comcast.net> wrote in message news:<0F********************@comcast.com>...
"John Viescas" <Jo***@nomail.please> wrote in message
news:e5**************@TK2MSFTNGP11.phx.gbl...

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
Nov 12 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.