Using the MIN function in the having clause

Question

I want to get the name of the employee who has the minimum salary. Is there a way to do this using only one query? I have given my query below, it doesn't work because the having clause requires a condition. Is there any way to give a condition in the having clause that will retreive the employee name with the minimum salary?

SELECT first_name,min(salary) as "sal"
FROM Employees
GROUP BY first_name 
having min(salary);

score 7 · Accepted Answer · edited Nov 04 '13 at 06:16

7

How about using ROWNUM?

SELECT *
FROM(SELECT first_name, salary
     FROM Employees
     ORDER BY salary
) WHERE ROWNUM = 1

edited Nov 04 '13 at 06:16

Jeffrey Kemp

59,135
14
106
158

answered Nov 04 '13 at 04:40

MarcinJuraszek

124,003
15
196
263

1

Thanks alot. But I was trying to do it in one query – Sindu_ Nov 04 '13 at 04:44
1

Sindu_, how many queries can you see? This is just one query. – Jeffrey Kemp Nov 04 '13 at 05:56
@JeffreyKemp I'm trying to do this without using a sub query – Sindu_ Nov 04 '13 at 05:57
1

What problem do you have with subqueries? They're one of the most powerful features of SQL. – Jeffrey Kemp Nov 04 '13 at 05:59
@JeffreyKemp I don't have a problem with it. I just wanted to know if its possible to do it without using a subquery – Sindu_ Nov 04 '13 at 06:08
1

The answer to that question will depend on what version of Oracle you're using. Oracle 12c supports TOP-N queries: http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php – Jeffrey Kemp Nov 04 '13 at 06:15
simplified the subquery – Jeffrey Kemp Nov 04 '13 at 06:16

score 4 · Answer 2 · edited Nov 04 '13 at 05:14

4

SELECT first_name, salary  as "sal" 
FROM   employees
WHERE  salary =(SELECT MIN(salary) 
                FROM   employees);

edited Nov 04 '13 at 05:14

Dba

6,511
1
24
33

answered Nov 04 '13 at 04:58

Nadee

67
3

2

Thanks. But I was trying to find a way to do it in a single query. – Sindu_ Nov 04 '13 at 04:59

score 3 · Answer 3 · edited May 23 '17 at 12:04

3

Try this solution, inspired from here:

SELECT e1.first_name, e1.salary AS "sal"
FROM Employees e1
LEFT OUTER JOIN Employees e2
ON (e1.id <> e2.id AND e1.salary > e2.salary)
WHERE e2.id IS NULL;

edited May 23 '17 at 12:04

Community

1
1

answered Nov 14 '13 at 20:47

evalarezo

1,134
7
13

score 2 · Answer 4 · answered Nov 14 '13 at 20:04

With a single SELECT statement:

SELECT MIN( first_name ) KEEP ( DENSE_RANK FIRST ORDER BY salary ASC, first_name ASC ) AS first_name,
       MIN( salary     ) KEEP ( DENSE_RANK FIRST ORDER BY salary ASC, first_name ASC ) AS salary
FROM   Employees;

SQLFIDDLE

However, if there are multiple people with the same minimum salary then this will only get the one with the name which is first alphabetically.

You can get all the names, but it does require multiple SELECT statements:

SELECT first_name, salary
FROM   Employees
WHERE  salary = ( SELECT MIN(salary) FROM Employees );

But having multiple SELECT statements isn't a bad thing.

SQLFIDDLE

score 1 · Answer 5 · edited Nov 04 '13 at 08:53

1

SELECT TOP 1 WITH TIES *
FROM employees
ORDER BY salary ASC

edited Nov 04 '13 at 08:53

Ben

51,770
36
127
149

answered Nov 04 '13 at 06:12

Nadee

67
3

1

This is incorrect; the OP is using Oracle and TOP is not valid syntax. – Ben Nov 04 '13 at 08:54

score 0 · Answer 6 · answered Nov 04 '13 at 04:41

0

If you need the employee with the lowest salary why don't you use Order By ..

SELECT top 1 first_name,min(salary) as LowestSalary
FROM Employees order by Salary asc

answered Nov 04 '13 at 04:41

MusicLovingIndianGirl

5,909
9
34
65

Thanks but I tried your query in SQL developer. It gives this error: 00923. 00000 - "FROM keyword not found where expected" – Sindu_ Nov 04 '13 at 04:51
2

This is incorrect; the OP is using Oracle and TOP is not valid syntax. – Ben Nov 04 '13 at 08:54

score 0 · Answer 7 · edited Jul 19 '23 at 02:16

0

Try this implementation:

SELECT first_name,min(salary) as "sal"
FROM Employees
GROUP BY first_name 
having min(salary) >0;

edited Jul 19 '23 at 02:16

Albin

1,000
1
11
33

answered Jun 26 '19 at 15:49

user63814

11
4

score 0 · Answer 8 · answered Nov 16 '22 at 17:09

If you want to do this with only one query, while also retrieving all employees with the minimum salary (example: you have a minimum salary of $40,000, but two employees have this exact salary) you could join the table with itself. This solution also uses the 'having' clause that you included in your original question.

SELECT e.first_name,e.salary AS "sal"
FROM Employees e, Employees e2
GROUP BY first_name 
HAVING MIN(e2.salary)=e.salary;

Using the MIN function in the having clause

8 Answers8

Linked