CODEWITHSUNDEEP
mysqldatemindate
Florencia Mo
Florencia Mo

Reputation: 11

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

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!

Upvotes: 0

Views: 847

Answers (2)

Gerry Ongko
Gerry Ongko

Reputation: 75

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.

Upvotes: 0

Tnadev
Tnadev

Reputation: 10112

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

Upvotes: 0

Related Questions