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)
Published on 12-Nov-2019 09:52:25
