Fetching first and last id in a query

[Heist]

Okay, here\'s what the query would look like in MSSQL:

 

SELECT *, (SELECT id FROM table ORDER BY id DESC LIMIT 1) AS Last, (SELECT id FROM table ORDER BY id ASC LIMIT 1) AS First FROM table WHERE id = XXX 

 

This query doesn\'t work in SQL, does anyone knows why? Thanks.

[pecoes]

MySQL doesn\'t support subqueries. But then again, sometimes it does:

SELECT * FROM table WHERE id=(SELECT MIN(id) FROM table) OR id=(SELECT MAX(id) AS last FROM table);

You will need MySQL 4.1 or later however. In earlier versions you could try:

SELECT @max_id:=MAX(id), @min_id:=MIN(id) FROM table;

SELECT * FROM table WHERE id=@max_id OR id=@min_id;

MySQL has its limitations, you know

[Heist]

MySQL doesn\'t support subqueries. But then again, sometimes it does:

SELECT * FROM table WHERE id=(SELECT MIN(id) FROM table) OR id=(SELECT MAX(id) AS last FROM table);

You will need MySQL 4.1 or later however. In earlier versions you could try:

SELECT @max_id:=MAX(id), @min_id:=MIN(id) FROM table;

SELECT * FROM table WHERE id=@max_id OR id=@min_id;

MySQL has its limitations, you know

 

Yeah, the more I use it, the more limitations I find. Thanks for the help.

[pecoes]

Well, MySQL is easier to learn than PostgresSql, Oracle or MSSQL and it\'s certainly faster.