TOP & "BOTTOM"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The "TOP" can easily be used to get records.

Is there a way to get something like a "BOTTOM 5" other
than entering Dates that will return the "BOTTOM 5"?

TIA - Bob
 
Gunny - Thank you. I thought of that...but I want to send that result (via
automation) to an Excel Chart. So, I would need to "reverse" the order.

Bob
 
Hi, Bob.

You're welcome.
I thought of that...but I want to send that result (via
automation) to an Excel Chart.
Click to expand...

It sounds like I didn't give you enough information to produce the results
that you are looking for. If you are creating this query via code and
exporting the results to Excel, then you'll need to alter the syntax for
that query by changing the ORDER BY clause.

For example, to display the 5 highest scores by using a descending sort
order:

SELECT TOP 5 LastName, FirstName, Score
FROM tblWinners
ORDER BY Score DESC;

.... and to display the 5 lowest scores by using an ascending sort order:

SELECT TOP 5 LastName, FirstName, Score
FROM tblWinners
ORDER BY Score ASC;

ASC is the abbreviation for ascending sort order, but since it's the
default, one can leave it out of the syntax and acheive the same results
(displaying the 5 lowest scores):

SELECT TOP 5 LastName, FirstName, Score
FROM tblWinners
ORDER BY Score;

Is this what you are looking for?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
What you send to Excel
could be *another* query
based on this "bottom 5" query,
but with "order reversed."

Maybe I don't understand.
 
SELECT T.LastName, T.FirstName, T.Score
FROM [SELECT TOP 5 LastName, FirstName, Score
FROM tblWinners
ORDER BY Score DESC]. as T

OR just return the primary key in a subquery.

SELECT T.LastName, T.FirstName, T.Score
FROM tblWinners as T
WHERE T.PrimaryKey in
(SELECT TOP 5 Tmp.PrimaryKey
FROM tblWinners as Tmp
ORDER BY tmp.Score DESC)
 
Back
Top