SQL Functions - TOP, DISTINCT

1. Why Use TOP and DISTINCT?

When querying data, you often need to:

The TOP clause (or its equivalents) and DISTINCT keyword help achieve these goals.

2. Retrieving a Fixed Number of Rows (TOP)

TOP is used in SQL Server to return the first N rows of a result set. Other databases use LIMIT or FETCH.

2.1 SQL Server Syntax

-- Show the top 3 highest-salaried employees
SELECT TOP 3 id, first_name, salary
FROM employees       -- table to query
ORDER BY salary DESC; -- sort by salary, highest first

Step-by-step:

SELECT TOP 3 id, first_name, salary: pick only three rows.

FROM employees: read from the employees table.

ORDER BY salary DESC: order rows by salary in descending order (largest to smallest).

Output

2.2 MySQL / PostgreSQL (LIMIT)

-- Equivalent in MySQL/PostgreSQL
SELECT id, first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;   -- pick first 3 rows after sorting

2.3 Oracle / SQL:2008 (FETCH)

-- Oracle or standard SQL
SELECT id, first_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;  -- take only first 3 rows

3. Removing Duplicate Rows (DISTINCT)

The DISTINCT keyword ensures each result value appears only once by removing duplicates.

3.1 Distinct on One Column

-- List unique departments
SELECT DISTINCT department
FROM employees;

This reads all department values, then filters out duplicates.

Output

3.2 Distinct on Multiple Columns

-- List unique department-salary pairs
SELECT DISTINCT department, salary
FROM employees;

Rows are considered duplicates only if both department and salary match.

Output

3.3 Counting Distinct Values

-- How many different departments?
SELECT COUNT(DISTINCT department) AS unique_departments
FROM employees;

Output

4. Combining TOP and DISTINCT

You can limit distinct results as well.

-- First 2 distinct departments alphabetically (SQL Server)
SELECT DISTINCT TOP 2 department
FROM employees
ORDER BY department;

-- MySQL equivalent
SELECT DISTINCT department
FROM employees
ORDER BY department
LIMIT 2;

Output

5. Advanced Features

Some databases offer special distinct or limiting behaviors.

5.1 PostgreSQL DISTINCT ON

-- Get highest-paid employee in each department
SELECT DISTINCT ON (department) department, first_name, salary
FROM employees
ORDER BY department, salary DESC;

Here, DISTINCT ON (department) returns one row per department, chosen by the ORDER BY clause.

5.2 SQL Server TOP ... WITH TIES

-- Top 3 salaries, including ties
SELECT TOP 3 WITH TIES first_name, salary
FROM employees
ORDER BY salary DESC;

If the 3rd and 4th salaries are equal, both appear.

6. Performance Tips

7. Summary

With TOP/LIMIT and DISTINCT, you can control the size and uniqueness of your query results. These tools help present clean, focused data to your applications and reports.