SQL LIMIT, TOP and FETCH FIRST: Select the First Rows

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

First define the order that makes first meaningful. ORDER BY amount DESC places larger known values ahead of smaller ones in engines whose descending NULL rule allows that, but default NULL positions vary. Filter unknown amounts when they should not compete in the ranking, or write an explicit missing-value sort key. Add id as a tie-breaker so equal amounts produce a predictable boundary. A limit of two is then a clear request for the first two rows in that total ordering.

LIMIT is commonly written after ORDER BY in PostgreSQL, MySQL and SQLite. TOP is placed immediately after SELECT in SQL Server, often as TOP (2). FETCH FIRST follows the ordering clause in PostgreSQL’s standard-style syntax. SQL Server uses OFFSET ... ROWS FETCH NEXT ... ROWS ONLY with ORDER BY for paged retrieval. The phrases look related, but copying the same statement into every engine is not a portability strategy.

Offset pagination skips a specified number of ordered rows, then returns the requested number. It is easy to understand, but large offsets can require processing many discarded rows. Concurrent inserts or deletes can also move page boundaries. Keyset pagination uses a comparison with the last seen sort key instead. When ordering by amount and id, the continuation rule must use both values and the same direction; saving only amount loses the information needed to distinguish tied sales.

With ties means the boundary may return more than the nominal row count. SQL Server TOP ... WITH TIES and PostgreSQL FETCH ... WITH TIES keep rows tied on the ORDER BY values. If id is included as a unique tie-breaker, no two rows tie on the complete order. Choose whether the business question means exactly two records or every record sharing a top value. Limiting results also does not guarantee that the database scans only that many source rows; a sort or complex filter may still examine much more data.

The shown query runs in PostgreSQL, MySQL and SQLite. For SQL Server use SELECT TOP (2) id,amount FROM sales WHERE amount IS NOT NULL ORDER BY amount DESC,id ASC. PostgreSQL can use FETCH FIRST 2 ROWS ONLY. MySQL and SQLite do not use that FETCH FIRST form.

Check the engine-specific syntax and version requirements in SQL Server TOP, PostgreSQL row limits.

Return the smallest two known amounts. Break ties using the sale id. Use LIMIT for PostgreSQL, MySQL or SQLite.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT id,amount FROM sales
WHERE amount IS NOT NULL
ORDER BY amount ASC,id ASC LIMIT 2;

id | amount
106 | 5
101 | 10

The explicit ascending order places five before ten, so sales 106 and 101 form the first page. No tie occurs in this page, but the id ordering still defines how later tied values would be handled. Unknown amounts are excluded rather than left to a dialect default.

Order only by amount descending and inspect the tied twenties, then restore id as a tie-breaker. Change LIMIT 2 to LIMIT 3 and predict sale 105 as the additional record. In SQL Server translate the limit to TOP (3); in PostgreSQL compare FETCH FIRST 3 ROWS ONLY. Each version should express the same known-amount ordering rather than merely return the same row count.