0

I have a query that returns three columns from the table employees_skills ordered by user_id and rating:

SELECT DISTINCT employees_skills.user_id, employees_skills.skill_id, employees_skills.rating 
FROM employees_skills
ORDER BY user_id DESC, rating DESC;

The result of such a query:

+---------+----------+--------+
| user_id | skill_id | rating |
+---------+----------+--------+
|     252 |       82 |      4 |
|     252 |       96 |      3 |
|     252 |        4 |      2 |
|     251 |       96 |      3 |
|     251 |       12 |      3 |
|     251 |       30 |      2 |
|     251 |       24 |      2 |
|     251 |       26 |      2 |
|     250 |       70 |      4 |
|     250 |       77 |      3 |
|     250 |       68 |      2 |
|     250 |       79 |      1 |
|     250 |       74 |      1 |
|     250 |       69 |      1 |
|     247 |       96 |      3 |
|     247 |       27 |      3 |
|     247 |       13 |      3 |
|     247 |       26 |      2 |
|     247 |       55 |      2 |
|     247 |       10 |      2 |

How to get the first three rows for each user_id and get the following result:

+---------+----------+--------+
| user_id | skill_id | rating |
+---------+----------+--------+
|     252 |       82 |      4 |
|     252 |       96 |      3 |
|     252 |        4 |      2 |

|     251 |       96 |      3 |
|     251 |       12 |      3 |
|     251 |       30 |      2 |

|     250 |       70 |      4 |
|     250 |       77 |      3 |
|     250 |       68 |      2 |

|     247 |       96 |      3 |
|     247 |       27 |      3 |
|     247 |       13 |      3 |
Matija Lukic
  • 599
  • 8
  • 28
  • I've solved this problem. The query that is most appropriate for this is explained here https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ at "Do it with user variables on MySQL" section. – Matija Lukic Jan 27 '20 at 15:14
  • Note that in more modern versions of MySQL, other solutions are available – Strawberry Jan 27 '20 at 16:57

0 Answers0