max query not working

gudii9
gudii9 used Ask the Experts™
on
SELECT Professor_Name, Salary FROM SALARIES WHERE Salary > (SELECT MIN(Salary) FROM SALARIES) * 4;
why above query not working
please advise
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Above query is syntactically correct and kindly explain why you mentioned it is not working either it is not returning the expected results or something else..

FYI, above query is trying to get list of all professor names whose salary is higher than 4 times the minimum salary across all professors.
If you want to calculate something else, kindly let us know so that we can help modify the query..
Geert GOracle dba
Top Expert 2009

Commented:
a query which returns no rows works
a query which gives a syntax error doesn't work

if it didn't work, what error did you get ?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
It appears you want anyone making more than 4 times the minimum salary.

Every time you think you need to select from the same table again, especially in an inline query, stop and rethink it.

See if this works for you:
SELECT Professor_Name, Salary from (
	SELECT Professor_Name, Salary, min(salary) over() * 4 four_time_min FROM SALARIES
) salaries
where salary > four_time_min
/

Open in new window


With all the new questions, I understand you are learning SQL.  The WINDOW functions are very powerful once you understand the syntax and what they are doing.

I suggest spending some time in the documentation and other online examples.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial