CODEWITHSUNDEEP
phpmysql
OLE
OLE

Reputation: 63

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

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.

Upvotes: 4

Views: 3357

Answers (2)

Calimero
Calimero

Reputation: 4288

The UNION operator allows this, carefully toying with the ORDER BY and LIMIT clauses :

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

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 8

Related Questions