Oh man. Want.

Shiny pretty.

Theoretical SQL question: Given a table with the columns employee number, department, and salary, can you create a SQL statement that lists the highest salary per department where the maximum salary is over $50K?

I get that you need to use the max function, a FROM subquery, and group by, but I'm blanking beyond that.

SELECT MAX(top_salary) FROM (SELECT salary AS top_salary FROM salary_table GROUP BY department) WHERE top_salary>50000

maybe?

I'm confused.

Do you want the highest salary of each department, but only if the maximum salary for that department is > 50,000?

Yes.

That's easy. Unless you want the employee number of the highest salary for each department as well. Then you need either a sub-select or the HAVING clause, I think.

So is my suggestion correct?

But you wan the department as well, right? (If it has a top salary > 50k) So it'd be like

SELECT Max(test.salary) AS MaxOfsalary, dept
FROM salary_table
GROUP BY salary_table.dept
HAVING (((Max(salary_table.salary))>50000));

eta: You don't really need the HAVING here. It could just be:

SELECT Max(salary_table.salary) AS MaxOfsalary,
salary_table.dept
FROM salary_table
WHERE (((salary_table.salary)>50000))
GROUP BY salary_table.dept;

eta: I just now noticed one place where I had my test table name instead....

ita, your subselect

SELECT salary AS top_salary FROM salary_table GROUP BY department

wouldn't work, as [salary] needs to be part of an aggregate function (MAX, etc.) if you're using a GROUP BY.

I really overthought that one...

Thanks.

No prob. I was overthinking it too at first. It does get more complex if you need the employee number too....