Chapter 14
The TOP Command
“But there are advantages to being elected President. The day after I was elected, I had my high school grades classified Top Secret.“
Table of Contents Chapter 14 – The TOP Command
– TOP Command is brilliant when ORDER BY Used
– How the TOP Command WITH TIES Decides
– The TOP Command will NOT work with Certain Commands
TOP Command
This is the TOP command. By putting TOP 3 before the column names, you are telling your system that you only want to see the top three rows on your report. This example brings back 3 random rows. That is not what makes the TOP command great. It becomes great when you ORDER BY the data.
TOP Command is brilliant when ORDER BY Used
It is incredibly important to use an ORDER BY statement with your TOP Command. By using an Order By, the TOP command becomes GREAT!
The TOP Command WITH TIES
SELECT TOP 2 WITH Ties
Last_Name, Class_Code, Grade_Pt
FROM Student_Table
ORDER BY Class_Code ;
By using the TOP WITH TIES Command, this will bring in the TOP amount along with ANY ties. So while you might only ask for the top 2 with ties, you might get 4 rows back. Why did 4 rows return here? Which row came back first?
How the TOP Command WITH TIES Decides
SELECT TOP 2 WITH Ties
Last_Name, Class_Code, Grade_Pt
FROM Student_Table
ORDER BY Class_Code ;
| Last_Name | Class_Code | Grade_Pt |
| Johnson | ? | ? |
| Larkins | FR | 0.00 |
| Thomas | FR | 4.00 |
| Hanson | FR | 2.88 |
Why did 4 rows return here? Which row came back first? Four rows returned with the first row coming back as a NULL for Class_Code. Then the next row returned was one of the Freshman. There were two other Freshman that tie. All ‘FR’ come back in a tie!
The TOP Command will NOT work with Certain Commands
SELECT TOP 3 *
FROM Student_Table ;
Because of the location of TOP within the SELECT and the elimination of some of the rows, TOP is NOT compatible with the following SQL Constructs.
* DISTINCT and QUALIFY
* WITH and WITH BY
* SAMPLE