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