select the first and the last two elements

Question

hi i have a table with this elements

id name
1 luke
2 johnny
3 perry
4 jenny
5 mark

I have to do a query that take the first element and the lasts 2 i this example

1 luke
4 jenny
5 mark

how can i do?

thanks

Answer 1

Take the union of Combine somehow (a) the top one, sorting ascending, (b) the top two, sorting descending.

Answer 2

I don't think you can do that with a single query : I'd say you'll have to use two queries :

One, to get the first result :

select *
from your_table
order by id asc
limit 1

And one other to get the two last results -- sorting in the opposite direction and getting the first two one will do the trick :

select *
from your_table
order by id desc
limit 2

After that, instead of doing two requests from your programming language to the SQL server, you could send only one query, that would use an UNION to get the results of both :

(select * from your_table order by id asc limit 1)
UNION
(select * from your_table order by id desc limit 2)

But, thinking about it... not sure this is actually possible, having a UNION with order by and limits in each sub-query...

---

EDIT : I did the test, and it's seems it's possible :

Here are the two queries, executed independantly :

mysql> select id, title from post order by id asc limit 1;
+----+--------------+
| id | title        |
+----+--------------+
|  1 | Premier post |
+----+--------------+
1 row in set (0,00 sec)

mysql> select id, title from post order by id desc limit 2;
+----+-------------------------+
| id | title                   |
+----+-------------------------+
|  7 | Septième post          |
|  6 | Sixième post (draft=7) |
+----+-------------------------+
2 rows in set (0,00 sec)

And here's what it looks like with the UNION :

mysql> (select id, title from post order by id asc limit 1) UNION (select id, title from post order by id desc limit 2);                                                                                                                                                       
+----+-------------------------+
| id | title                   |
+----+-------------------------+
|  1 | Premier post            |
|  7 | Septième post          |
|  6 | Sixième post (draft=7) |
+----+-------------------------+
3 rows in set (0,03 sec)

Note, though, that the order of the 3 resulting rows is not quite well defined...

And, quoting the following manual page of MySQL 5.1 : 12.2.8.3. UNION Syntax :

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows.

Answer 3

In two queries:

select * from table order by id asc limit 1
select * from table order by id desc limit 2

I'm not sure if you can do it in 2 queries in mysql. You could do it this way in ms-sql:

select * from table order by id asc limit 1
union all
select * from table order by id desc limit 2

Answer 4

Well, it's not pretty to do it in one query (especially since MySQL doesn't support LIMIT in IN subqueries), but it's possible (but subqueries are kind of cheating):

SELECT id, name
FROM table
WHERE id = (SELECT id FROM table ORDER BY id LIMIT 1)
    OR id = (SELECT id FROM table ORDER BY id DESC LIMIT 1)
    OR id = (SELECT id FROM table ORDER BY id DESC LIMIT 1,1)