Есть таблица users [ id (int), email (str), score (int), company_id(int) ]
со связью «один ко многим» с таблицей companies [ id(int), name(str) ]
Задание
Получить выборку [ id, email, score, company_id ] с максимальным score по каждой компании.
Users
| ID | score | company_id | |
|---|---|---|---|
| 1 | [email protected] | 8 | 1 |
| 2 | [email protected] | 8 | 2 |
| 3 | [email protected] | 4 | 1 |
| 4 | [email protected] | 1 | 2 |
| 5 | [email protected] | 2 | 1 |
| 6 | [email protected] | 3 | 2 |
| 7 | [email protected] | 0 | 1 |
| 8 | [email protected] | 6 | 2 |
| 9 | [email protected] | 9 | 1 |
Result
| ID | score | company_id | |
|---|---|---|---|
| 9 | [email protected] | 9 | 1 |
| 2 | [email protected] | 8 | 2 |