How to limit a copy to one record; not the entire table

Boy! did I buy the farm!

Still learning though. Thanks for your time & knowledge.

"Douglas J. Steele" wrote:

I used "runner" because that's what Ken's example was. (presumably he was
storing the results of races in a table named Runners)

"Runner" has no relevance to Access, and no, "TOP" has nothing to do with
how long it took to find a record.

From the Help file:

TOP n [PERCENT]

Returns a certain number of records that fall at the top or the bottom of a
range specified by an ORDER BY clause. Suppose you want the names of the top
25 students from the class of 1994:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you don't include the ORDER BY clause, the query will return an arbitrary
set of 25 records from the Students table that satisfy the WHERE clause.The
TOP predicate doesn't choose between equal values. In the preceding example,
if the twenty-fifth and twenty-sixth highest grade point averages are the
same, the query will return 26 records.

You can also use the PERCENT reserved word to return a certain percentage of
records that fall at the top or the bottom of a range specified by an ORDER
BY clause. Suppose that, instead of the top 25 students, you want the bottom
10 percent of the class:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

The ASC predicate specifies a return of bottom values. The value that
follows TOP must be an unsigned Integer.

TOP doesn't affect whether or not the query is updatable.

What got me was Run time vs Runtime and the urge to over-think! Always a challenge; when to think;
when to go home.

"Chris O'C via AccessMonster.com" u29189@uwe wrote:

Thanks for the laugh and the reminder that no matter how well we think we've
explained a concept, it can still be misinterpreted.

Ken's example of Runtimes is "how much time passed from start of race to when
runner crossed the finish line" and is stored in the column name RunTime.
The query example tries to answer the question of "who are the 3 slowest
runners?" by finding the 3 lowest times to complete the race. (If Ken had
sorted the without the "desc" he would have been asking "who are the 3
fastest runners?") Has nothing to do with how long it takes to run the query
and find those 3 slowest runners. Doug was pointing out that if there are
ties, the query will return more than 3 rows.

The top 1 returns all rows matching the maximum value - if the sort is
descending. If it's only 1 row in the table with the maximum value, that
will result in 1 row returned by the query, but if there are 82 rows with the
same maximum value the top 1 query will return those 82 rows.

Chris
Microsoft MVP


wrote:

My interpretation, correct if appropriate, "TOP" is a metric that is an indication of a measurement
of the (run) time it took to "find" a record?

Also, that TOP 1 is not the TOP one-record-only but all records with a run time between 1 and 2 ?

EagleOne

Remember, though, that you have to be careful with SELECT TOP n. TOP n does
NOT necessarily only return the first n rows.

In your example, should the top runner have a RunTime of 1.03, two more
runners have times of 1.05 and three more runners have times of 1.06, your
query would actually return 6 rows, not 3.