/python/mySQL/r/javascript/

What I cannot create, I do not understand. — Richard Feynman

January 11, 2018

How to Select the First and Last Record from a Query

When working with MySQL, you’ll often need to retrieve the first and last records from a table. .

For example: Whether you’re looking for the earliest and most recent orders placed by a customer, the first and last customer entries, or the starting and ending salary values in an employee’s history table.

You can achieve this easily with ORDER BY, LIMIT, and sometimes aggregate functions like MIN() and MAX().

This tutorial will show you three different methods to accomplish this task, from basic to advanced, with clear examples and explanations of when to use each approach.

Method 1: Using ORDER BY and LIMIT

To get the first record, you order your data in ascending order based on a specific column (like an ID) and limit the results to one row.

Example A: Fetch the first customer by ID

SELECT * 
FROM customers
ORDER BY customer_id ASC
LIMIT 1;

How this syntax works:

The ORDER BY customer_id ASC clause sorts all records by the customer_id in ascending order (from smallest to largest).
The LIMIT 1 clause then returns only the first row from the sorted result.

Example B: Fetch the earliest order based on date:

SELECT * 
FROM orders
ORDER BY order_date ASC
LIMIT 1;

This query returns the earliest order by sorting the records from the oldest order_date to the newest and then selecting the top result.

2. Selecting the Last Record

Similarly, to get the last record, you order your data in descending order based on your chosen column and use LIMIT 1 to return the top row.

Example A: Fetch the last customer by ID

SELECT * 
FROM customers
ORDER BY customer_id DESC
LIMIT 1;

This returns the customer with the highest customer_id record.

Example B: If you want to Fetch the most recent order by date:

SELECT * 
FROM orders
ORDER BY order_date DESC
LIMIT 1;

This returns the order with the latest order_date

Selecting Both the First and Last Record in Single Query

You can also fetch both the first and last records together in a single result set using a UNION ALL operation to combine two seperate queries.

For Example C: Fetch the earliest and the most recent order

(
  SELECT *, 'Earliest' as record_type 
  FROM orders 
  ORDER BY order_date ASC 
  LIMIT 1
)
UNION ALL
(
  SELECT *, 'Latest' as record_type 
  FROM orders 
  ORDER BY order_date DESC 
  LIMIT 1
);

How it works:

The first subquery fetches the earliest order by sorting dates in ascending order and taking the first result.
The second fetches the most recent order by sorting dates in descending order and taking the first reult.
UNION ALL combines the results of these two subqueries into a single output.

3. Using Subqueries for Flexibility

You can use subqueries with aggregate functions like MIN() and MAX() to find the first and last records based on a date or ID when working with analytics.

Example: Get the first and last salary entry for an employee:

Suppose you want to find the earliest and most recent salary enteries from an employee’s history table.

SELECT *
FROM employee_salaries
WHERE salary_date = (
    SELECT MIN(salary_date) FROM employee_salaries
)
OR salary_date = (
    SELECT MAX(salary_date) FROM employee_salaries
);

How the syntax works:

The subquery (SELECT MIN(salary_date) FROM employee_salaries) calculates the very first date in the table.
The subquery (SELECT MAX(salary_date) FROM employee_salaries) calculates the very last date.
The main query then selects all rows where the salary_date matches either of these two values.

Note: This method will return all rows that share the earliest date and all rows that share the latest date. If multiple entries exist on the minimum or maximum date, you may get more than two rows. To guarantee only one “first” and one “last” record, use the ORDER BY ... LIMIT approach.

Ensuring Exactly One First and One Last Record

Sometimes your dataset may have multiple rows with the same earliest or latest date. For example, imagine two employees receiving a salary update on the exact same day. If you simply use MIN() or MAX(), or filter by the earliest/latest date, you’ll get all rows from that date instead of just one.

To guarantee that you always get exactly one first record and one last record, you should use ordering with a tie-breaker (such as a unique id).

Example: First and Last Record with Tie-Breaker

In the employee_salaries table, assume we want the earliest and latest salary entries. We’ll use both salary_date and the unique id column to break ties.

-- Get the earliest entry 
-- (if two rows share the same salary_date, pick the one with the lowest id)
SELECT *
FROM employee_salaries
ORDER BY salary_date ASC, id ASC
LIMIT 1;

-- Get the latest entry
-- (if two rows share the same salary_date, pick the one with the highest id)
SELECT *
FROM employee_salaries
ORDER BY salary_date DESC, id DESC
LIMIT 1;

🔎 Explanation:

ORDER BY salary_date ASC → sorts by date from earliest to latest.
id ASC → ensures that if two rows share the same date, the row with the smallest id is chosen as the earliest.
ORDER BY salary_date DESC, id DESC → sorts from latest to earliest, breaking ties with the largest id.
LIMIT 1 → ensures you only get a single row as the result.

Combining into One Query

To fetch both results in one query, you can use UNION ALL:

(
  SELECT *
  FROM employee_salaries
  ORDER BY salary_date ASC, id ASC
  LIMIT 1
)
UNION ALL
(
  SELECT *
  FROM employee_salaries
  ORDER BY salary_date DESC, id DESC
  LIMIT 1
);