CODEWITHSUNDEEP
mysqlsql
Simon S.
Simon S.

Reputation: 39

How to get the first item and the last item in a single sql statement

I was wondering how to grab the first item and the last item in a MySQL database. I know these two lines will do the work, but I am trying to limit it down to one line.

SELECT * FROM `test_table` order by ID asc limit 0,1
SELECT * FROM `test_table` order by ID desc limit 0,1

For example there is a table called test_table and it has 5 rows. I would like to get row number 1 and row number 5 in the same statement. This will save me from using many PHP if statements and will shorten up my code. Any help would be appreciated.

Upvotes: 0

Views: 904

Answers (3)

Aakash Johari
Aakash Johari

Reputation: 11

You can use the min and max ID if it's monotonically increasing -

SELECT * FROM `test_table`
WHERE ID IN ((SELECT MIN(ID) FROM `test_table`), (SELECT MAX(ID) FROM `test_table`));

I'd suggest running the explain for the query to see if it performs as per your expectation.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The following SQL does not get the first item:

SELECT * FROM `test_table` limit 0,1

It returns an arbitrary row. SQL tables and result sets are unordered, so limit without order by returns an indeterminate row.

The syntax to do what you want is:

(SELECT t.* FROM `test_table` t ORDER BY id ASC LIMIT 1)
UNION ALL
(SELECT t.* FROM `test_table` t ORDER BY id DESC LIMIT 1);

Note: If the table has only one row, this returns two rows, because the same row is the "first" and "last". If you want only one in this situation, you could use UNION rather than UNION ALL.

Upvotes: 4

Jens
Jens

Reputation: 69470

use union it will combine both sql queries and you the expected result

SELECT * FROM `test_table` limit 0,1
union
SELECT * FROM `test_table` order by ID desc limit 0,1

Upvotes: 1

Related Questions