Jump to content


Photo

Fetching first and last id in a query


  • Please log in to reply
3 replies to this topic

#1 Heist

Heist
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 14 October 2003 - 03:23 PM

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.
PANCAKES!

#2 pecoes

pecoes
  • Members
  • Pip
  • Newbie
  • 2 posts
  • Locationamsterdam, nl

Posted 14 October 2003 - 05:48 PM

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
Everything becomes interesting if you look at it long enough. Sometimes that\'s a good thing.

#3 Heist

Heist
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 14 October 2003 - 05:51 PM

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.
PANCAKES!

#4 pecoes

pecoes
  • Members
  • Pip
  • Newbie
  • 2 posts
  • Locationamsterdam, nl

Posted 14 October 2003 - 06:01 PM

Well, MySQL is easier to learn than PostgresSql, Oracle or MSSQL and it\'s certainly faster.
Everything becomes interesting if you look at it long enough. Sometimes that\'s a good thing.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users