MySQL - How to select Minimum AND Maximum in one (UNION) Query

Question

Can anyone tell me how to select the minimum AND the maximum from a table using the MIN and MAX and UNION keywords.

I tried to use:

SELECT ename, MIN(sal)
FROM emp
UNION
SELECT ename, MAX(sal)
FROM emp;

But it's only displaying the same name of the MIN, like:

smith | 800
smith | 5000

Needs to be:

smith | 800
king  | 5000

Looney

How do you handle ties (i.e. two people with same minimum salary) – Snubnosed

I am going to guess this is on mysql, because the lack of group by would be flagged up on a number of databases as an error, whilst mysql is going to do the behaviour you are seeing. – Weave

You forgot GROUP BY in your two queries (also, you might want UNION ALL instead of UNION, otherwise you'll get just 1 row in the case where max=min) – Aerodrome

Answer 1

SELECT ename, sal
FROM EMP
WHERE sal = (SELECT MIN(sal) FROM EMP)
UNION
SELECT ename, sal
FROM EMP
WHERE sal = (SELECT MAX(sal) FROM EMP)

Rori

What happens if there are two salaries that are the same for max or min? – Philanthropic

Answer 2

How about:

SELECT ename, sal
FROM emp
ORDER BY sal ASC
LIMIT 1
UNION
SELECT ename, sal
FROM emp
ORDER BY sal DESC
LIMIT 1

Heads

I think that this is oracle (is oracle database sample schema). Limit is a MySQL keyword – Rheostat

Perhaps, but the tag says mysql; I was responding based on that. – Heads

Actually, it looks like that was edited in by someone else... Now I don't know what to believe! – Heads

Answer 3

Try this

select Name as Lowest,amt as salary
from emp 
join (select min(sal) as amt from emp) x on x.amt=emp.sal
union all
select Name ,amt as salary
from emp 
join (select max(sal) as amt from emp) x on x.amt=emp.sal

Snubnosed

Answer 4

Didn't actually test it, but this general idea should work:

SELECT ename, sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp)
UNION
SELECT ename, sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp)

Note that this can return more than 2 rows if there is more than one row with the (same) MIN or MAX value. It could also return just one row if both MIN and MAX happen to come from the same row (you could use UNION ALL to avoid that).

BTW, your query means: Get MIN(sal) and then get the ename from a random row (and the same for MAX). In both MIN and MAX cases the MySQL decided to return the same random row.

However, in most databases other than MySQL, you could'n even have this kind of "random" query - you'd be required to include the ename in GROUP BY.

Damper

Answer 5

If you are looking for the name of employee with min sal then your query is:

Select name of employee that her sal is them min sal of all employees:

Select name, sal from employees where sal = 
   (select min(sal) from employees)

You can union this query to another one to get max.

Good look with yours oracle homeworks.

Rheostat

Answer 6

select Name, LifeExpectancy from (
(select * from(
SELECT Name, LifeExpectancy FROM country where LifeExpectancy > 0 ORDER BY LifeExpectancy ASC LIMIT 1
)a)
union
(select * from(
SELECT Name, LifeExpectancy FROM country where LifeExpectancy > 0 ORDER BY LifeExpectancy desc LIMIT 1
)b)
)c

Result:

+---------+----------------+
|  Name   | LifeExpectancy |
+---------+----------------+
| Zambia  |           37.2 |
| Andorra |           83.5 |
+---------+----------------+

Cheung

Answer 7

select name,max(salary) from employee where salary not in(select max(salary) from employee);

o/p:-amit 65000 Ex o/p:madhu 6500

Answer: select name,salary from employee where salary =(select max(salary) from employee where salary not in(select max(salary) from employee)); o/p: madhu 6500

Megilp

Why are posting an answer to a very old question (that already has numerous replies) and then thanking yourself for the answer? – Plasia