Get the value of the earliest and the latest date in mysql

Question

I have a table that shows the delivery day that customers choose, but this can change and the table adds a new record per change, so I want a table that gets the first value they choose and the last one they choose.

The table is something like this:

user_id	Day	DateUpdate
1	Friday	2021/07/01
3	Sunday	2021/07/01
3	Tuesday	2021/07/15
4	Monday	2021/07/02
4	Wednesday	2021/07/18
5	Thursday	2021/07/12
7	Monday	2021/07/01
7	Wednesday	2021/07/16

And the result I want should be this:

User_id	first_day	last_day
1	Friday	Friday
3	Sunday	Tuesday
4	Monday	Wednesday
5	Thursday	Thursday
7	Monday	Wednesday

What function should I Use? Could you help me please? Thank you so much!

Answer 1

First of all, the day name can actually be found using the DAYNAME() function, so there is actually no need to store it in another column. But anyways, try this code.

SELECT 
user_id,
DAYNAME(MIN(DateUpdate)) as first_day,
DAYNAME(MAX(DateUpdate)) as last_day
FROM tbl
GROUP BY user_id

You could also remove the DAYNAME() function if you just want the dates.

Answer 2

SELECT INTO TABLE

MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension. Instead, MySQL Server supports the INSERT INTO ... SELECT standard SQL syntax, which is basically the same thing.

INSERT INTO tbl_temp2 (user_id,first_day,last_day)
SELECT tbl_temp1.user_id,tbl_temp1.first_day,tbl_temp1.last_day
FROM tbl_temp1 
WHERE <Need to add your selection criteria here>;

Reference doc