Getting the last n records

To retrieve the first 100 records in a table, I can use the following query:

SELECT TOP 100 MyField FROM MyTable

But how can I get the last 100 records?

Firstly, it is worth pointing out that this question has no meaning unless the result set is ordered on some field.

If an ORDER BY clause is not specified, then SQL does not guarantee that records will be returned in any particular order. So asking for the last 100 records could theoretically return the same results as would be obtained by asking for the first 100 records.

If an ORDER BY clause is specified, then using TOP will return the TOP n records after sorting has occurred.

If I have the following table of names:

then the query

SELECT TOP 3 GivenName FROM Names
ORDER BY GivenName

will return

Bob
Jane
Jill

To return the last 3 names, the records must be sorted in descending order:

SELECT TOP 3 GivenName FROM Names
ORDER BY GivenName DESC

will return

Steven
Patsy
Ondrej

To return the last 3 names, but in ascending order, use a subquery:

SELECT GivenName FROM Names
WHERE GivenName IN
(SELECT TOP 3 GivenName FROM Names
ORDER BY GivenName DESC)
ORDER BY GivenName

This query may return more than 3 names if GivenName is not unique.

If another "Ondrej" is added to the table, then the above query will return

Ondrej
Ondrej
Patsy
Steven

To limit the output to exactly 3 records requires some decision about which Ondrej to include.

For the sake of this example, I will add an identity column to the Names table:

The following query will make an arbitrary choice between the two Ondrejs, and will output exactly 3 records:

SELECT GivenName FROM Names
WHERE NameID IN
(SELECT TOP 3 NameID FROM Names
ORDER BY GivenName DESC)
ORDER BY GivenName

Adelle.