The first and last record for the user for a certain period of time in one request

Question

I am looking to get the first and last record for a given user_id over a period of time, such as 24 hours.

I know this can be done using two queries, doing something like this, and then switching ORDER BY ASC / DESC .

 SELECT id, user_id, date, other_columns FROM table WHERE user_id = 1 AND date > DATE_SUB(CURDATE(), INTERVAL 24 HOUR) ORDER BY date DESC LIMIT 1 

However, I am wondering if this can be done using a single query.

Answer 1

This is what you might think:

 SELECT t.id, t.user_id, t.date, t.other_columns FROM table t WHERE user_id = 1 AND date = ( SELECT MIN(date) FROM table WHERE user_id = t.user_id AND date > DATE_SUB(CURDATE(), INTERVAL 24 HOUR)) UNION ALL SELECT id, user_id, date, other_columns FROM table WHERE user_id = 1 AND date = ( SELECT MAX(date) FROM table WHERE user_id = t.user_id AND date > DATE_SUB(CURDATE(), INTERVAL 24 HOUR))