I am trying to display the name of the worker(s) with the largest average marks.
My first table is the worker table and stores the worker_id and worker_name. The second table is the test table and stores the worker_id of who took the test, the test_id, and the marks.
My goal is to be able to print the name of the worker who has the highest average marks across all tests. This is what I have so far:
select max(avg_mark) as max_avg
select worker_name, avg(mark) as avg_mark
from worker join test worker.worker_id = test.worker_id
group by worker_name
order by avg(mark) desc);
You are close. The idea is to select the worker who's average matches the max found in a separate query. Here is another one that works:
select master.worker_name, mx.max_mark from (select worker_name, avg(mark) as avg_mark from worker inner join test on worker.worker_id = test.worker_id group by worker_name) master inner join (select max(mark) as max_mark from test) mx on master.avg_mark = mx.max_mark