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

Question

I'm working on php small project, here I need first 5 records from beginning of records and last record 1 from end of the table's record. I don't know how to write a single mysqli query. Any help will be appreciated. Thanks in advance.

Answer 1

SQL tables represent unordered sets. So, there is no such thing as the first five rows or last row -- unless a column explicitly defines the ordering.

Often, a table has some sort of auto-incremented id column, which 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, an insert date/time column is the appropriate column to use.
• You want to use union all rather than union -- unless you want to incur the overhead of removing duplicate values.
• For this formulation, if there are fewer than 6 rows, then you will get a duplicate.