How to Select the First Row From the MySQL Table

1. Select the First Row From the MySQL Table
2. Use the LIMIT Clause to Retrieve the First Row From the MySQL Table Where a Column Has Multiple Instances
3. Use IN(), MIN(), and GROUP BY to Grab the First Row in Each Group From the MySQL Table
4. Use LIMIT and ORDER BY to Select the First Row From the Whole MySQL Table

Today, we will explore three scenarios and their solutions where we want to select the first row from the MySQL table.

In the first scenario, we will learn to fetch the first row from the MySQL table where a particular column has multiple instances. For instance, select the first row from the manager table where the first_name is Mehvish.

In the second scenario, we will select the first row in each group of a table. Here, we will also see how to retrieve data with respect to the groups. In the third scenario, we will see how to grab the first row from the whole MySQL table.

Select the First Row From the MySQL Table

Before diving into the code examples, let’s create a manager_id table with manager_id, first_name, and last_name as fields where the manager_id is a primary key.

The queries for creating and populating this table are given below. You may also use these.

Example code:

CREATE TABLE manager (
    manager_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(150) NOT NULL,
    last_name VARCHAR(150) NOT NULL,
    PRIMARY KEY(manager_id)
);

INSERT INTO manager (first_name, last_name)
VALUES
('Mehvish', 'Ashiq'),
('Saira', 'Mushtaq'),
('Thomas', 'Christopher'),
('Thomas', 'Gabrial'),
('Tahir', 'Raza'),
('Saira', 'Johny'),
('Saira', 'Daniel');

SELECT * FROM manager;

Output:

+------------+------------+-------------+
| manager_id | first_name | last_name   |
+------------+------------+-------------+
|          1 | Mehvish    | Ashiq       |
|          2 | Saira      | Mushtaq     |
|          3 | Thomas     | Christopher |
|          4 | Thomas     | Gabrial     |
|          5 | Tahir      | Raza        |
|          6 | Saira      | Johny       |
|          7 | Saira      | Daniel      |
+------------+------------+-------------+
7 rows in set (0.00 sec)

Use the LIMIT Clause to Retrieve the First Row From the MySQL Table Where a Column Has Multiple Instances

We have one instance of Mehvish, one instance of Tahir, two instances of Thomas, and three instances of Saira in the first_name column of the manager table. You can use the SELECT statement to look at the current table data.

Execute the following statement to grab the first row from the manager table where the first_name is Saira.

Query:

SELECT * FROM manager WHERE first_name = 'Saira' LIMIT 1;

Output:

+------------+------------+-----------+
| manager_id | first_name | last_name |
+------------+------------+-----------+
|          2 | Saira      | Mushtaq   |
+------------+------------+-----------+
1 row in set (0.00 sec)

Suppose we want to grab the third record where the first_name is Saira. We use the LIMIT clause with two arguments (explained later in this tutorial).

Query:

SELECT * FROM manager WHERE first_name = 'Saira' LIMIT 2,1;

Output:

+------------+------------+-----------+
| manager_id | first_name | last_name |
+------------+------------+-----------+
|          7 | Saira      | Daniel    |
+------------+------------+-----------+
1 row in set (0.00 sec)

Assume we want to get the first two records where the first_name is Saira. We can do that as follows.

Query:

SELECT * FROM manager WHERE first_name = 'Saira' LIMIT 2;

Output:

+------------+------------+-----------+
| manager_id | first_name | last_name |
+------------+------------+-----------+
|          2 | Saira      | Mushtaq   |
|          6 | Saira      | Johny     |
+------------+------------+-----------+
2 rows in set (0.00 sec)

We can also get the last two records where the first_name name is Saira. We use the ORDER BY clause with the LIMIT clause.

Query:

SELECT * FROM manager WHERE first_name = 'Saira' ORDER BY manager_id DESC LIMIT 2;

Output:

+------------+------------+-----------+
| manager_id | first_name | last_name |
+------------+------------+-----------+
|          7 | Saira      | Daniel    |
|          6 | Saira      | Johny     |
+------------+------------+-----------+
2 rows in set (0.00 sec)

Here, it is crucial to understand the role of the LIMIT and ORDER BY clauses. The LIMIT clause is used with a SELECT statement to get a certain number of records from a table.

The LIMIT clause takes one or two arguments that must be a positive integer or zero. The following is the syntax of the LIMIT clause with one and two arguments.

Syntax:

# Syntax with one argument
SELECT
    your_select_list
FROM
    you_table_name
WHERE
    your_condition
LIMIT row_count;

# Syntax with two arguments
SELECT
    your_select_list
FROM
    your_table_name
WHERE
    your_condition
LIMIT [offset,] row_count;

The row_count shows the record’s maximum number to be returned, while the offset is used when we want to retrieve a specific range of records, for instance, from the offset 2 to row_count 4. Remember that the offset of the first row is not 1 but 0.

See the following illustration.

Remember, the LIMIT row_count and the LIMIT 0, row_count is equivalent to each other. Depending on the project requirements, the ORDER BY clause sorts the table data in descending or ascending order.

Use IN(), MIN(), and GROUP BY to Grab the First Row in Each Group From the MySQL Table

This second scenario will select and grab the first row in each group from the table using the IN() and MIN() functions and the GROUP BY clause.

Example code:

SELECT * FROM manager
WHERE
manager_id IN (
    SELECT min(manager_id)
    FROM manager
    GROUP BY first_name
);

Output:

+------------+------------+-------------+
| manager_id | first_name | last_name   |
+------------+------------+-------------+
|          1 | Mehvish    | Ashiq       |
|          2 | Saira      | Mushtaq     |
|          3 | Thomas     | Christopher |
|          5 | Tahir      | Raza        |
+------------+------------+-------------+
4 rows in set (0.08 sec)

This example uses the different functions and clauses to get the desired results.

1. IN() - This function lets us specify multiple values in the WHERE clause. It returns 1 if an expression is equal to any of the IN() list values.
2. GROUP BY - It groups the records that contain the same values, mainly used with aggregate functions, for instance, MIN(), MAX(), COUNT(), etc.
3. MIN() - It returns the lowest value of an attribute (column) in the query.
4. Subquery - It is nested inside another query, for instance, SELECT, UPDATE, DELETE, etc. We can nest a subquery to another subquery as well (we need to understand the execution order of various clauses).

Use LIMIT and ORDER BY to Select the First Row From the Whole MySQL Table

Select the first row from the whole table irrespective of order and condition using the LIMIT clause.

Query:

SELECT * from manager LIMIT 1;

Output:

+------------+------------+-----------+
| manager_id | first_name | last_name |
+------------+------------+-----------+
|          1 | Mehvish    | Ashiq     |
+------------+------------+-----------+
1 row in set (0.00 sec)

Use the ORDER BY with LIMIT to get the first row from the sorted data (in descending order).

Query:

SELECT * from manager ORDER BY first_name DESC LIMIT 1;

Output:

+------------+------------+-------------+
| manager_id | first_name | last_name   |
+------------+------------+-------------+
|          3 | Thomas     | Christopher |
+------------+------------+-------------+
1 row in set (0.00 sec)