I have a profile and jobs table
Then there is the following query
SELECT COUNT(jobs.id) as jobs_completed,
profile.rating,
profile.id
FROM profile
JOIN jobs ON jobs.user_id = profile.user_id AND jobs.ended <> 0
WHERE profile.whoami = 'contractor' HAVING jobs_completed > 5
ORDER BY rating DESC, jobs_completed DESC
LIMIT 6
The query is working with id | jobs_completed | rating as input
| id | jobs_completed | rating |
|---|---|---|
| 1 | 52 | 2.8 |
| 2 | 16 | 4.6 |
| 3 | 1 | 5.0 |
| 4 | 8 | 4.7 |
| 5 | 12 | 4.3 |
| 6 | 5 | 4.9 |
| 7 | 26 | 4.4 |
| 8 | 57 | 4.9 |
The expected output is
| id | jobs_completed | rating |
|---|---|---|
| 8 | 57 | 4.9 |
| 4 | 8 | 4.7 |
| 2 | 16 | 4.6 |
| 7 | 26 | 4.4 |
| 5 | 12 | 4.3 |
| 6 | 5 | 4.9 |
The problem is that the output of this query is only producing one resulting row when I am expecting 6.
How should I write the query so that the produced output is according to the highest ratings first and then the highest jobs_completed second ONLY WHEN the jobs_completed is > 5 OR there is no jobs_completed that are > 5 remaining in the input ?