SQL : get Min and Max value in one column

Question

I have an employee table with a name and a salary.

I want to print these 2 columns with only 2 records in them, the name of my highest and lowest payed employee.

It should look something like this:

Name      Salary  
------------------
James       800  
Samanth    3000

The following code is NOT what I want, I want the min and the max in 2 columns with 2 names representing each value

SELECT 
    name, MIN(salary), MAX(salary)
FROM
    employee

Tamikotamil

what if more than 1 person have max/min salary – Graceless

good question, i will look in to it but right now this issue has priority – Tamikotamil

Answer 1

I Finally figured out a simple code for what i wanted.

select emp_name, salary
from employees
where salary = (select max(salary) from employees)
union all
select emp_name, salary
from employees
where salary = (select min(salary) from employees);

I didn't know about Union. Thank you all for your contribution

Tamikotamil

Answer 2

You can do this by using a subquery:

SELECT first_name, salary FROM employees
WHERE salary IN ((SELECT MAX (salary) FROM employees), (SELECT MIN (salary) FROM employees))

Stratify

Answer 3

The best method depends on the database, but the following uses standard SQL:

select max(case when seqnum_asc = 1 then salary end) as min_salary,
       max(case when seqnum_asc = 1 then name end) as min_salary_name,
       max(case when seqnum_desc = 1 then salary end) as max_salary,
       max(case when seqnum_desc = 1 then name end) as max_salary_name      
from (select e.*,
             row_number() over (order by salary asc) as seqnum_asc,
             row_number() over (order by salary desc) as seqnum_desc
      from employee e
     ) e;

Unbosom

Could you possibly give a sample of what your code would output? – Tamikotamil

Answer 4

There are many ways you can do this. This is just one of it. Also it may give more than 2 records if they have same min/max salary

declare @min int, @max int select @min=min(Salary),@max=max(Salary) from YourTable

SELECT Name, Salary from YourTable where Salary=@min OR Salary=@max

Perish

Answer 5

This way I used. You can do like this

DECLARE @minsalary varchar(60)
set @minsalary = (select top 1 concat(Name, ' ', salary) from employee where salary= (select min(salary) from employee) 
)

DECLARE @maxsalary varchar(60)
set @maxsalary = (select top 1 concat(Name, ' ', salary) from employee where salary= (select max(salary) from employee) 
)

select concat(@maxsalary, ' ', @minsalary)

Vansickle

Answer 6

You can do the following if you are using mysql

1. Group concat all users by salary 1st
2. List of users with minimum salary using 'order by limit 1'
3. Do same as step 2 for maximum salary
4. UNION them

This is will solve the issue even if more than 1 person have max/min salary

SELECT * 
FROM   ( 
                SELECT   group_concat(NAME) AS names , 
                         salary , 
                         'minimum' AS remarks 
                FROM     employee 
                GROUP BY salary 
                ORDER BY salary limit 1)tmp 
UNION ALL 
SELECT * 
FROM   ( 
                SELECT   group_concat(NAME) AS names , 
                         salary , 
                         'maximum' AS remarks 
                FROM     employee 
                GROUP BY salary 
                ORDER BY salary DESC limit 1
    )tmp1

Sample output

names       salary  remarks
tom,jolly   10      minimum
sally     10001   maximum

Graceless

Answer 7

you can try this. example output.

name      MAX(salary)   MIN(salary)
George    9200          9200
James     5000           100
Kanye     3200          3200
Nicole    4500          4500
Samanth   3000          2300
Umut      1500           250
Vladimir  2300           330

example table values

id  name      salary
 1  James        800
 2  Samanth     3000
 3  Umut        1500
 4  Umut         250
 5  Nicole      4500
 6  George      9200
 7  Kanye       3200
 8  Vladimir    2300
 9  Vladimir    1000
10  Vladimir     330
11  James       5000
12  James        100
13  Samanth     2300

SELECT name, MAX(salary), MIN(salary) FROM employee GROUP BY name;

Pyrogenous

thanks but this is exactly what i do not want, i don't want to have 3 columns but 2 columns – Tamikotamil