sql_func_first

SQL: Retrieving the First Row or Top N Rows

Getting the “First” Row: Importance of Ordering

In SQL, tables are inherently unordered sets of rows. To reliably retrieve what you consider the “first” row, you must use an ORDER BY clause to define a specific sequence. Without ORDER BY, the database might return rows in any order, which can change between queries.

Once ordered, you can use various SQL clauses to limit the result set to the first row (or the top N rows).

Note: The non-standard FIRST() function found in MS Access is not part of standard SQL. Below are common, standard, or widely supported methods.

Standard and Common Syntaxes for Retrieving the First Row

Standard SQL (SQL:2008 and later): FETCH FIRST N ROWS ONLY

This is the ANSI SQL standard way. Supported by PostgreSQL, Oracle (12c+), DB2, SQL Server (2012+ via OFFSET ... FETCH), and others.

SELECT column_name(s)
FROM table_name
ORDER BY sort_column [ASC|DESC]
FETCH FIRST 1 ROW ONLY;

Example: Get the oldest customer.

SELECT CustomerName, RegistrationDate
FROM Customers
ORDER BY RegistrationDate ASC
FETCH FIRST 1 ROW ONLY;

MySQL & PostgreSQL: LIMIT Clause

SELECT column_name(s)
FROM table_name
ORDER BY sort_column [ASC|DESC]
LIMIT 1;

Example: Get the customer with the alphabetically first name.

SELECT CustomerName
FROM Customers
ORDER BY CustomerName ASC
LIMIT 1;

SQL Server: TOP Clause

SELECT TOP 1 column_name(s)
FROM table_name
ORDER BY sort_column [ASC|DESC];

Example: Get the top-priced product.

SELECT TOP 1 ProductName, Price
FROM Products
ORDER BY Price DESC;

Oracle (Older versions, pre-12c): ROWNUM Pseudocolumn

This method requires a subquery because ROWNUM is assigned before ORDER BY in the same query block.

SELECT column_name(s)
FROM (
  SELECT column_name(s)
  FROM table_name
  ORDER BY sort_column [ASC|DESC]
)
WHERE ROWNUM <= 1;

Example: Get the first customer by CustomerID using ROWNUM.

SELECT CustomerName
FROM (
  SELECT CustomerName, CustomerID
  FROM Customers
  ORDER BY CustomerID ASC
)
WHERE ROWNUM <= 1;

Demonstration Database: Northwind

We’ll use the Northwind sample database for context.

Below is a selection from the “Customers” table:

CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country


1
Alfreds Futterkiste
Maria Anders
Obere Str. 57
Berlin
12209
Germany


2
Ana Trujillo Emparedados y helados
Ana Trujillo
Avda. de la Constitución 2222
México D.F.
05021
Mexico


3
Antonio Moreno Taquería
Antonio Moreno
Mataderos 2312
México D.F.
05023
Mexico


4
Around the Horn
Thomas Hardy
120 Hanover Sq.
London
WA1 1DP
UK


5
Berglunds snabbköp
Christina Berglund
Berguvsvägen 8
Luleå
S-958 22
Sweden

Example: Finding the First Customer Alphabetically

The following SQL statement selects the first customer alphabetically by “CustomerName” from the “Customers” table, using the LIMIT clause (common in MySQL/PostgreSQL).

SELECT CustomerName AS FirstAlphabeticalCustomer
FROM Customers
ORDER BY CustomerName ASC
LIMIT 1;

Practical Application: Retrieving the most recent entry, the highest scoring player, or the top-selling product are common use cases that rely on ordering and limiting results.