min query issues

gudii9
gudii9 used Ask the Experts™
on
SELECT  COUNT (*) from SALARIES  WHERE salary>(SELECT  MIN (Salary ) from SALARIES )


somet reason above i am getting wrong count
instead of 6 i am getting 15

please advise on how to optimize and make it better
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:
>> instead of 6 i am getting 15

Query seems correct and no need for further optimization..
Can you kindly explain with some test data to show why it should return 6 instead of 15 so that we can help modify the query accordingly.
Geert GOracle dba
Top Expert 2009
Commented:
min(salary) is 1 record (or more records with same salary)

it's returning everything except the first record
awking00Information Technology Specialist
Commented:
Please post the salary values that you queried.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Agree with the above experts.  Keep in mind that experts here cannot connect to your data sources and run queries, so we are entirely dependent on what you provide us in the question to provide an answer, and that includes sample data.

Otherwise why 15 is being returned instead of 6 ... no idea.

Off of top of my head though (as Geert states above) if you are filtering by the minimum value of Salary then I would expect the return set to have the same number of rows in the table itself minus those rows where the salary = minimum value, and minus any NULLs.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Also agree that we cannot help without seeing the data.

As I a posted in your MAX question:  If you think you need to query the table twice, rethink it.

This should return the same results and only access the table once:
SELECT  COUNT (*) from
(
	select salary, min(salary) over () min_salary from tab1
) salaries
WHERE salary> min_salary
/

Open in new window


Without the data you have, I cannot say if it will return 6 or 15 rows.

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