Order date records and fetch the 2nd ordered record in MySQL

To order, use ORDER BY and to fetch only the 2nd ordered record, use MySQL LIMIT and set offset as well. Let us first create a −

mysql> create table DemoTable1417
   -> (
   -> CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> CustomerName varchar(20),
   -> ShippingDate date
   -> );
Query OK, 0 rows affected (1.10 sec)

Insert some records in the table using insert −

mysql> insert into DemoTable1417(CustomerName,ShippingDate) values('Chris','2019-01-21');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1417(CustomerName,ShippingDate) values('David','2018-12-01');
Query OK, 1 row affected (0.55 sec)
mysql> insert into DemoTable1417(CustomerName,ShippingDate) values('Carol','2019-09-28');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1417(CustomerName,ShippingDate) values('Sam','2019-08-29');
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select −

mysql> select * from DemoTable1417;

This will produce the following output −

+------------+--------------+--------------+
| CustomerId | CustomerName | ShippingDate |
+------------+--------------+--------------+
|          1 | Chris        | 2019-01-21   |
|          2 | David        | 2018-12-01   |
|          3 | Carol        | 2019-09-28   |
|          4 | Sam          | 2019-08-29   |
+------------+--------------+--------------+
4 rows in set (0.00 sec)

Here is the query to fetch the 2nd ordered record −

mysql> select CustomerName,ShippingDate from DemoTable1417 order by CustomerId limit 2,1;

This will produce the following output −

+--------------+--------------+
| CustomerName | ShippingDate |
+--------------+--------------+
| Carol        | 2019-09-28   |
+--------------+--------------+
1 row in set (0.00 sec)

AmitDiwan

Published on 12-Nov-2019 05:22:25

• Related Questions & Answers
• Fetch ordered records after a specific limit in MySQL
• Fetch date record that equals today in MySQL
• How to order records and fetch some using MySQL LIMIT?
• Select last 20 records ordered in ascending order in MySQL?
• Display all records ignoring the current date record in MySQL
• Fetch date records comparing with the current date’s day and month in MySQL
• Fetch a single ordered date from a column with MySQL LIMIT
• MySQL query to fetch the latest date from a table with date records
• Calling NOW() function to fetch current date records in MySQL?
• Fetch records from interval of past 3 days from current date in MySQL and add the corresponding records
• Get the returned record set ordered by (ORDER BY) position in MySQL 'IN' clause
• MySQL order by 0 first and then display the record in descending order?
• MySQL query to fetch date records greater than the current date after adding days with INTERVAL?
• Fetch student records whose result declared 12 days before the current date in MYSQL
• Order MySQL records randomly and display name in Ascending order