Level 73
You use the aggregate max for that.
Jan 12, 2021
3
Level 8
How to get latest date of each row (when it's duplicated)
Hello , this is the Career model
| id | user_id | company_id | date |
|----|---------|------------|------------|
| 1 | 10 | 78 | 2015-09-17 |
| 2 | 10 | 152 | 2010-09-17 |
| 3 | 35 | 152 | 2020-09-17 |
| 4 | 7 | 152 | 2018-09-17 |
| 5 | 13 | 152 | 2020-09-17 |
| 6 | 13 | 152 | 2018-09-01 |
| 7 | 17 | 152 | 2018-09-08 |
I want to return only the latest date of each user_id to get something like :
| id | user_id | company_id | date |
|----|---------|------------|------------|
| 1 | 10 | 78 | 2015-09-17 |
| | | | |
| 3 | 35 | 152 | 2020-09-17 |
| 4 | 7 | 152 | 2018-09-17 |
| 5 | 13 | 152 | 2020-09-17 |
| | | | |
| 7 | 17 | 152 | 2018-09-08 |
Then get only the results where company_id = 152:
| id | user_id | company_id | date |
|----|---------|------------|------------|
| | | | |
| | | | |
| 3 | 35 | 152 | 2020-09-17 |
| 4 | 7 | 152 | 2018-09-17 |
| 5 | 13 | 152 | 2020-09-17 |
| | | | |
| 7 | 17 | 152 | 2018-09-08 |
Level 8
@tray2 do you have any example ?
Level 73
Something like this
SELECT user_id, company_id, MAX(date) date
FROM careers
GROUP BY user_id, comapany_id;
Please or to participate in this conversation.