Jump to content

Archived

This topic is now archived and is closed to further replies.

Heist

Fetching first and last id in a query

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.