MySQL - How to select Minimum AND Maximum in one (

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

Answer 1

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

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

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

Answer 4

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 |
+---------+----------------+

Answer 5

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)

Answer 6

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.