It is surprising that within T-SQL there is a TOP() but no BOTTOM(). So how can we find the bottom of a set?

Lets first recap on what TOP() is meant to achieve. Assume that there is a table called Ducks which has information on Award Winning ducks (Let us never question how they or what they have won these awards for). There are 3 columns, Name, NumberOfAwards and Category. If we win a simple SELECT on the table we get,

Name              NumberOfAwards             Category
-------            -------------------       -----------
Barry               10                       1
Ivan                4                        1
Steve               15                       3
Mandy               12                       2

If we run the following

SELECT TOP(1) NumberOfAwards
FROM Ducks

the data return is thus,

NumberOfAwards             Name
-------------------       -----------
15                        Steve
Steve is returned because he the duck the highest score,15.

Using TOP(2) would return,

NumberOfAwards             Name
-------------------       -----------
15                        Steve
10                        Barry

Steve and Barry are returned because they are the top 2 award winners. You get the idea.

Now, what if you want to get the bottom Award winner. There is no BOTTOM() so how can we grab this data. TOP() is affected by the ordering of data. The following T-SQL will return the bottom award winners

SELECT TOP(1) NumberOfAwards
FROM Ducks
ORDER BY NumberOfAwards asc -- sorting the data in ascending

NumberOfAwards             Name
-------------------       -----------
4                          Ivan

This works because the data is ordered in the following fashion using ASC

NumberOfAwards             Name
-------------------       -----------
4                          Ivan
10                         Barry
12                         Mandy
15                         Steve

Using the ASC order puts Ivan at the top. When TOP(1) is used it will grab this top value in the list.

If you data was ordered using DESC then the top value would be retrieved. Try it!