CODEWITHSUNDEEP
sqlgroupwise-maximum
Tsunami
Tsunami

Reputation: 141

SQL : get Min and Max value in one column

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

Upvotes: 6

Views: 32575

Answers (7)

SuhailY
SuhailY

Reputation: 69

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

Upvotes: 2

Tsunami
Tsunami

Reputation: 141

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

Upvotes: 7

umutyerebakmaz
umutyerebakmaz

Reputation: 1037

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;

Upvotes: 0

sumit
sumit

Reputation: 15464

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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;

Upvotes: 1

Hien Nguyen
Hien Nguyen

Reputation: 18975

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)

Upvotes: 0

Prasool
Prasool

Reputation: 1

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

Upvotes: 0

Related Questions