JIYIK CN >

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

In the first scenario, we will learn to get the first row from a MySQL table where there are multiple instances of a particular column. For example, select the first row from the manager table where first_name is Mehvish.

In the second scenario, we will select the first row in the table per group. Here we will also see how to retrieve group-related data. In the third scenario, we will see how to get the first row from the entire MySQL table.

Select first row from 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, with manager_id being the primary key.

Given below are the queries to create and populate this table. You can also use these.

Sample 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 LIMIT clause to retrieve first row from MySQL table where one 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 view the current table data using the SELECT statement.

Execute the following statement to get the first row whose first_name is Saira from the manager table.

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 fetch the third record where first_name is Saira. We use the LIMIT clause (explained later in this tutorial) with two parameters.

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)

Suppose we want to get the first two records where first_name is Saira. We can do it like this.

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 is Saira. We use the ORDER BY clause along with the LIMIT clause.

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 LIMITand ORDER BYclauses. LIMITThe clause SELECTis used with the statement to fetch a certain number of records from a table.

LIMITThe clause takes one or two arguments which must be positive integers or zero. Following is the syntax of the LIMIT clause with one and two arguments.

grammar:

# 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;

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

See the picture below.

Remember, LIMIT row_count and LIMIT 0, row_count are equivalent. ORDER BYThe clause sorts the table data in descending or ascending order, depending on the project requirements.

Get the first row in each group from a MySQL table using IN(), MIN(), and GROUP BY

The second case uses the IN() and MIN() functions along with the GROUP BY clause to select from the table and get the first row in each group.

Sample 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 different functions and clauses to achieve the desired results.

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

Use LIMIT and ORDER BY to select the first row from the entire MySQL table

Use the LIMIT clause to select the first row from the entire table, regardless of order and condition.

SELECT * from manager LIMIT 1;

Output:

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

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

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)