PostgreSQL ORDER BY and LIMIT for Beginners (Guide)

PostgreSQL ORDER BY sorts the rows a query returns, and LIMIT tells PostgreSQL how many of those rows to actually give you. Put together, ORDER BY LIMIT is how you answer questions like "show me the 5 newest signups" or "who are the top 3 highest-paid employees?". This guide walks through both clauses from scratch, with small examples you can copy into your own database and run right away.

What you need before you start

You just need PostgreSQL installed and a way to run SQL, such as the psql command-line tool or a GUI like pgAdmin. If you have not installed PostgreSQL yet, or you are new to psql, read our beginner guide on installing PostgreSQL and using psql first. This tutorial also assumes you already know the basics of SELECT, which we cover in our guide on selecting data in PostgreSQL.

Create a sample table

To follow along, create a small table called products and add a few rows. Copy this into psql or your SQL editor.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC NOT NULL,
  created_at DATE NOT NULL
);

INSERT INTO products (name, price, created_at) VALUES
  ('Notebook', 4.50, '2026-01-10'),
  ('Desk Lamp', 19.99, '2026-02-14'),
  ('Backpack', 39.00, '2026-01-22'),
  ('Water Bottle', 12.75, '2026-03-01'),
  ('Headphones', 59.90, '2026-02-05');

CREATE TABLE products (...) builds a table with four columns: id (a number that auto-increments, thanks to SERIAL), name and created_at (text and date), and price (a NUMERIC column for money, which avoids rounding errors). The INSERT statement adds five sample products. Now we have real data to sort and limit.

Sorting rows with ORDER BY

By default, PostgreSQL does not guarantee any particular row order. If you want rows sorted, you must say so with ORDER BY. Here is how to list products from cheapest to most expensive.

SELECT name, price
FROM products
ORDER BY price;

SELECT name, price FROM products picks two columns from the table. ORDER BY price sorts the result rows by the price column. When you do not specify a direction, PostgreSQL sorts in ascending order (smallest to largest) by default, so the Notebook at $4.50 appears first.

Sorting in descending order with DESC

To reverse the order, from largest to smallest, add the keyword DESC after the column name. Use ASC if you ever want to be explicit about ascending order, although it is the default.

SELECT name, price
FROM products
ORDER BY price DESC;

This runs the same query as before, but DESC flips the sort direction, so the Headphones at $59.90 now come first. DESC stands for "descending"; its opposite is ASC for "ascending".

Sorting by more than one column

You can sort by several columns at once. PostgreSQL sorts by the first column, and only uses the next column to break ties.

SELECT name, price, created_at
FROM products
ORDER BY created_at ASC, price DESC;

This sorts rows by created_at first, from the oldest date to the newest. If two products were added on the exact same date, price DESC decides which one comes first between them, showing the more expensive one first.

Limiting rows with LIMIT

LIMIT restricts how many rows a query returns. It is usually combined with ORDER BY, because "give me the top 3 rows" only makes sense once you have decided what "top" means by sorting first.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

ORDER BY price DESC sorts products from most expensive to cheapest, and LIMIT 3 keeps only the first three rows of that sorted result. This returns the three priciest products: Headphones, Backpack, and Desk Lamp.

Skipping rows with OFFSET

OFFSET tells PostgreSQL to skip a number of rows before it starts counting the LIMIT. This is the standard way to build pagination, such as "page 2" of a product list.

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 2 OFFSET 2;

ORDER BY price ASC sorts products from cheapest to most expensive. OFFSET 2 skips the first two rows (the two cheapest products), and LIMIT 2 then returns the next two rows after that. This is effectively "page 2" if each page shows 2 products.

Combining WHERE, ORDER BY, and LIMIT

In a real query you will often filter rows with WHERE, then sort them, then limit them. PostgreSQL always applies them in this order: filter first, sort second, limit last.

SELECT name, price
FROM products
WHERE price < 50
ORDER BY price DESC
LIMIT 2;

WHERE price < 50 keeps only products cheaper than $50, which removes the Headphones. ORDER BY price DESC then sorts what is left from most expensive to cheapest, and LIMIT 2 keeps just the top two: Backpack and Desk Lamp.

Clause order matters

SQL clauses must appear in a fixed order in your query, even though PostgreSQL actually processes them in a different order behind the scenes. Here is a quick reference.

Common mistakes to avoid

• Using LIMIT without ORDER BY when you expect a specific row order — the result can vary between runs.
• Writing LIMIT before ORDER BY in the query — PostgreSQL requires ORDER BY to come first.
• Forgetting that DESC only applies to the column it is written next to, not the whole query, when sorting by multiple columns.
• Using OFFSET with very large numbers on big tables, which can slow queries down; an indexed column in the ORDER BY clause helps performance.

Practice it yourself

Try writing a query that finds the two oldest products (by created_at) that cost more than $10. You will need WHERE to filter on price, ORDER BY on created_at, and LIMIT 2. Experimenting with your own small table is the fastest way to make ORDER BY and LIMIT feel natural.