How can I get the first 5 and last 1 records from mysql table?

Question

I am working on a small php project, here I need to first 5 records from the beginning of the records and the last record 1 from the end of the table record. I do not know how to write a single mysqli query. Any help would be appreciated. Thanks in advance.

Answer 1

SQL tables are unordered sets. Thus, there are no such things as the first five rows or the last row - unless the column explicitly defines the order.

Often a table has a kind of column with an auto-increment identifier that can be used for this purpose. If so, you can do:

(select t.*
 from t
 order by id asc
 limit 5
) union all
(select t.*
 from t
 order by id desc
 limit 1
);

Notes:

• Sometimes the insert date / time column is the corresponding column to use.
• union all, union - .
• , 6, .

Answer 2

UNION , ORDER BY LIMIT:

(SELECT * FROM table ORDER BY field ASC LIMIT 5)
UNION
(SELECT * FROM table ORDER BY field DESC LIMIT 1)