TOP Function

The TOP N function produces

• The top (or bottom) N rows of results based on specified criteria.
• The top (or bottom) N percentage of rows based on specified criteria.
• These results either with or without ties (more than one row has identical criteria values).
• Sample rows arbitrarily without regard to rankings or order.

New syntax to the SQL lexicon:

TOP {decimal | integer} [PERCENT] [WITH TIES]

* TOP N where N is an integer up to 18 digits in length.

Below are the options with TOP.

• TOP 10 – Return the top ten rows according to criteria
• TOP 15 PERCENT – Return the top 15% of rows according to criteria
• TOP 10 WITH TIES – If more that one row has the same criteria value, return all
• TOP 15 PERCENT WITH TIES – If more than one row has the same criteria value, return all

Example 1:
SELECT TOP 5
  department_number
  ,budget_amount
  FROM department
  ORDER BY 2 DESC;

Result
department_number    budget_amount
-------------------------    -------------
                401              982300.00
                403              932000.00
                301              465600.00
                100              400000.00
                501              308000.00

Example 2:
SELECT TOP 5 WITH TIES
department_number
, budget_amount
FROM department
ORDER BY 2 DESC;

 department_number    budget_amount
--------------------------    -------------
              401                 982300.00
              403                 932000.00
              301                 465600.00
              100                 400000.00
              501                 308000.00
              402                 308000.00
           
Things to notice:

• Even though TOP 5 is specified, six rows are returned.
• Because there is a tie for the fifth position, both rows are returned.
• This only occurs when WITH TIES is specified.
• WITH TIES returns multiple tied rows when there is a tie for the 'last' position.
• It will return all rows containing the 'tied' value, but it will only count it as one row.
• Tied rows which are not in the last position, are each counted separately toward the N total.
• The same result could have been retuned by specifying TOP 6 without the WITH TIES option.

Now, consider getting the rows at the bottom of an ordered list. This is accomplished by using the ORDER BY clause.

Example:
Show the bottom three employees by salary.
SELECT TOP 3
employee_number
, salary_amount
FROM employee ORDER BY salary_amount ASC;

 employee_number    salary_amount
 ----------------------    -------------
           1014               24500.00
           1013               24500.00
           1001               25525.00
       
The TOP N function may also be used to return unordered rows. This is accomplished by using TOP N without including an ORDER BY clause.

Select two random rows.
SELECT TOP 2
employee_number
, salary_amount
FROM employee;

employee_number    salary_amount
----------------------    -------------
           1008             29250.00
           1012             37900.00
         

Using the PERCENT Option
The TOP N function can also produce a percentage of rows in addition to an absolute number of rows.

Return employees whose salaries represent the top ten percent.

SELECT TOP 10 PERCENT
employee_number
,salary_amount
FROM employee ORDER BY salary_amount DESC;

 employee_number   salary_amount
  -------------------    -------------
            801            100000.00
           1017            66000.00
           1019            57700.00

Things to notice:
10% of 26 rows is 2.6 rows rounded to 3.
PERCENT must be a number between 0 and 100.
At least one row is always returned (if there is at least one row in the table).

A percentage resulting in a fractional number of rows is always rounded up:10% of 6 rows = .6 rows = 1 row output
20% of 6 rows = 1.2 rows = 2 rows output
30% of 6 rows = 1.8 rows = 2 rows output

TOP N Parameterized
TOP n processing also permits the value of n to be passed into the operation by means of a parameterized macro.

Create a parameterized macro which shows the top n budget amounts.

 CREATE MACRO Top_Budgets (n INT) AS
(SELECT TOP :n
  department_number
  , budget_amount
  FROM department
  ORDER BY 2 DESC;);

Using the macro, show the top five department budgets.

EXEC Top_Budgets(5);

   department_number   budget_amount
  ----------------------    -------------
                401            982300.00
                403            932000.00
                301            465600.00
                100            400000.00
                501            308000.00