MySQL - How to select Minimum AND Maximum in one (

问题:

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

回答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)

回答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

回答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

回答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.

回答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.

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

回答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