2

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 ?

NoChance
  • 5,632
  • 4
  • 31
  • 45
StartupNation
  • 21
  • 3
  • What about your current query is NOT working as is? Seems like you just need to reverse the fields in your `order by` clause to get `rating` as the primary sort, and `jobs_completed` seondary. Also, it's not clear to me why you're doing a subquery instead of a join. – Marc Sep 15 '22 at 01:47
  • @Marc Updated the query as per your comment. The output is only producing one row, when I am expecting 6 – StartupNation Sep 15 '22 at 01:54
  • @NoChance It's MariaDB-5.4 (MySQL 5.7), and no chance, limit 6 doesn't change anything! Also, it's the same column. – StartupNation Sep 15 '22 at 04:05
  • This will give you the answer: https://stackoverflow.com/questions/28497082/mysql-aggregate-functions-without-group-by-clause – Sam M Sep 15 '22 at 04:12
  • @SamM may be right, in SQL aggregate functions require GROUP BY, not sure about MariaDB? – NoChance Sep 15 '22 at 04:39
  • just wonder why id = 6 will be the last one. as in your order by, id = 6 should be after id = 8 isn't? because it has the same rating – learning Sep 15 '22 at 05:46
  • is it returning six rows without the ORDER BY, and only one when ORDER BY is added? That would be a MariaDB bug then, but I assume that actually both variants return a single row only? – Hartmut Holzgraefe Sep 15 '22 at 07:47
  • I figured it out. But @learning it's because of `HAVING jobs_completed > 5`. Your scenario would be true if it was `>=` – StartupNation Sep 18 '22 at 05:46

0 Answers0