MySql Query example using Aggregate function

Question

Doing some practice example queries in MySQL and I am stuck on one.

Here is the relevant table:

create table instructor (
    ID          varchar(5),
    name        varchar(20) not null,
    dept_name   varchar(20),
    salary      numeric(8,2) check (salary > 29000),
    primary key (ID),
    foreign key (dept_name) references department(dept_name)
        on delete set null
) ENGINE = INNODB;

The question is: for each instructor, get their name, their salary and the number of instructors who earn more than they do.

Naturally, my mind goes to using the COUNT() aggregate function. I have come up with this answer (I know I added the ID column but it doesn't matter):

SELECT DISTINCT T.ID, T.name, T.salary, COUNT(S.ID) AS num
FROM instructor AS T, instructor AS S
WHERE S.salary>T.salary
GROUP BY T.ID;

But the issue here is that it leaves out the row for the instructor who earns the highest salary, as he is excluded in the WHERE clause.

I thought about making the WHERE clause greater than OR equal to and just decrementing each number but is that even possible?

I also thought that something like this would work:

Select DISTINCT T.ID, T.name, T.salary, S.salary as salary2, count(S.salary) as num
FROM instructor as T, instructor as S
WHERE S.salary>T.salary
group by T.ID
having S.salary>T.salary;

I've gotta be missing something easy here.

Answer 1

Use a subquery instead. It will also obviate the GROUP BY, etc.

Select  T.ID, T.name, T.salary, T.salary,
        ( SELECT count(*)
               FROM instructor AS S 
               WHERE S.salary > T.salary
        ) as num
    FROM  instructor as T;

Note

COUNT(*) is the usual way to say it; COUNT(X) counts how many Xs are not-NULL.

Answer 2

Given the following set of data:

SELECT ins.id, ins.name, ins2.id AS earns_more_emp_id, ins2.name AS earns_more_emp_name, ins2.salary AS earns_more_emp_salary 
FROM instructors ins
LEFT OUTER JOIN instructors ins2 on ins2.salary > ins.salary
ORDER BY ins.id ASC, ins2.salary ASC

That will give you the highest earner (EMP 10).

Answer 3

You can do this using window functions, without joining and only scanning the base table once

SELECT
  i.ID,
  i.name,
  i.salary,
  COUNT(*) OVER (ORDER BY i.salary DESC ROWS UNBOUNDED PRECEDING) - 1 AS earns_more
FROM instructor i;

You could also do this with a slightly different window frame, but I think this is less efficient

COUNT(*) OVER (ORDER BY i.salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS earns_more