Finding first/last records from a SELECT

[lurtnowski]

I have a query to show all contents from a table

SELECT rack_id FROM racks WHERE location = "Watch Floor";

How do I get the first and last records rack_id?

Last

SELECT rack_id
FROM racks
ORDER BY id DESC
LIMIT 1

First

SELECT rack_id
FROM racks
ORDER BY id ASC
LIMIT 1

But can I do that in 1 query?

[r937]

@lurtnowski

But can I do that in 1 query?

sure

SELECT MIN(id) AS first
     , MAX(id) AS last
  FROM racks

[sibertius]

@lurtnowski

But can I do that in 1 query?

Combine them

SELECT rack_id
FROM racks
ORDER BY id DESC
LIMIT 1
UNION
SELECT rack_id
FROM racks
ORDER BY id ASC
LIMIT 1

OR

SELECT
(SELECT rack_id FROM racks ORDER BY id DESC LIMIT 1) as first,
(SELECT rack_id FROM racks ORDER BY id ASC LIMIT 1) as last
FROM racks

OR

even smarter look at r937 SQL Consultant

[r937]

technically speaking, a UNION query can have only one ORDER BY clause, so if your example actually works, you must be using MySQL

also, LIMIT might not work in every database system

[sibertius]

@r937

technically speaking, a UNION query can have only one ORDER BY clause

I prefer using sqlfiddle both in question and answer to avoid typo.