CodeRang
SQL topics

SQL ORDER BY, DISTINCT and LIMIT

SQL ORDER BY controls row order, DISTINCT removes duplicate result rows, and LIMIT restricts how many rows are returned. They are often used together, but each clause solves a different problem.

Updated · SQL Arena team

SQL from scratch courseAll problems

What you will practice

  • Removing duplicate rows
  • Sorting by multiple columns
  • Using ASC and DESC
  • Limiting result size
  • Creating deterministic output

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Return the first five unique department-role pairs

Use departments and employees. Return unique department and role combinations as department and role. Sort by department and then role, and keep the first five rows.

Query 1PostgreSQLv16
SELECT DISTINCT d.name AS department,
       e.role
FROM departments d
JOIN employees e ON e.department_id = d.id
ORDER BY department, e.role
LIMIT 5;
Submit
Resultdepartmentsemployees
Answer accepted

How DISTINCT, ORDER BY and LIMIT work together

The join produces one row per employee, so the same department-role pair can appear more than once. Analytics has two Analysts, and Platform has two Backend Engineers. SELECT DISTINCT removes those duplicate combinations. DISTINCT applies to the full selected row, not to one column independently.

ORDER BY department, e.role uses two sort keys. Rows are first ordered by department. When the department is the same, role determines the order inside that group. ASC is the default direction, while DESC reverses it. Multiple keys are important when you want a stable and predictable top-N result.

LIMIT 5 is applied after ordering and keeps five rows. This is why LIMIT alone does not mean 'the first five' in any meaningful business sense. Without ORDER BY, SQL does not guarantee which qualifying rows appear first. PostgreSQL also supports FETCH FIRST 5 ROWS ONLY as an alternative form.

SQLPostgreSQL v16
SELECT DISTINCT d.name AS department,
       e.role
FROM departments d
JOIN employees e ON e.department_id = d.id
ORDER BY department, e.role
LIMIT 5;
Query result
departmentrole
AnalyticsAnalyst
AnalyticsData Engineer
DesignDesigner
FinanceAccountant
PlatformBackend Engineer

Common ORDER BY and DISTINCT mistakes

01

Using LIMIT without ORDER BY

LIMIT controls quantity, not priority. If you need the newest ten, highest ten, or alphabetically first ten rows, define that rule explicitly in ORDER BY.

02

Using DISTINCT to hide a bad join

DISTINCT can make duplicate rows disappear, but it does not repair an incorrect relationship between tables. If a join multiplies rows unexpectedly, inspect the join condition first.

03

Ignoring ties and NULL values

Rows with the same first sort value need another key if their relative order matters. NULL ordering also varies by database behavior and can be controlled explicitly with NULLS FIRST or NULLS LAST where supported.

Practice problems on this topic

SQL from scratch course

Use DISTINCT when the result needs unique values or unique combinations of selected columns. Use ORDER BY when row sequence matters. LIMIT reduces the final number of rows, while OFFSET can skip a number of rows, for example LIMIT 20 OFFSET 40. PostgreSQL also supports FETCH FIRST N ROWS ONLY. The key rule is that top-N queries need an explicit ordering if 'top' or 'first' has a real meaning. SQL Arena includes 1,192 auto-checked tasks across PostgreSQL, MySQL, ClickHouse, and BigQuery. The free tier provides 292 tasks. Relevant practice includes over-the-counter medications, endangered species, venue types, wards for a particular practitioner, a busiest-day ClickHouse task, festival ROI, the last row in a group, and a ClickHouse problem that finds the service with the most errors.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

How does SQL ORDER BY work?

ORDER BY sorts result rows using one or more expressions. ASC sorts ascending and is usually the default, while DESC sorts descending. Additional columns can be added to resolve ties.

What does DISTINCT do in SQL?

SELECT DISTINCT removes duplicate result rows. With multiple selected columns, SQL evaluates uniqueness across the entire combination of those column values.

How do I return the first 10 rows in SQL?

In PostgreSQL, use ORDER BY with LIMIT 10 when the identity of the first ten rows matters. LIMIT 10 without ORDER BY restricts the count but does not guarantee which rows are returned first.

What is the difference between LIMIT and OFFSET?

LIMIT defines the maximum number of returned rows. OFFSET skips a number of rows before the result is returned. They are often combined for pagination, usually with an ORDER BY clause.

See also

Practice sorting and limiting results

Solve tasks where you need to choose DISTINCT, multiple ORDER BY keys, or a row limit yourself. The SQL sandboxes run directly in the browser.

Practice ORDER BY

More practice from CodeRang