how to select a column which is not listed in group by clause-CodePudding

select the id from the table based on highest salary from each dept

table

Id	dept	salary
1	support	25000
2	support	20000
3	development	35000
4	development	25000
5	development	30000

select Id from table
where salary In (select max(salary) from table group by dept)

If run query like this I am getting output like

Id
1
3
4

CodePudding user response：

We can return value of dept column, along with maximum salary of each department:

SELECT m.dept
     , MAX(m.salary) AS max_salary 
  FROM mytable m
 GROUP
    BY m.dept

We can turn that into an inline view (or "derived table") and join that to the original table. We can get the detail rows that match rows (on dept and maximum salary)

SELECT t.id
--   , t.dept
--   , t.salary
  FROM ( 
         -- maximum salary from each dept
         SELECT m.dept
              , MAX(m.salary) AS max_salary 
           FROM mytable m
          GROUP
             BY m.dept

       ) s
  JOIN mytable t
    ON t.dept   = s.dept
   AND t.salary = s.max_salary
 ORDER
    BY t.id

Note that if there are two (or more) rows in mytable with the same maximum salary for the same dept, both (or all) of the matching rows will be returned.

As a demonstration, consider what will be returned by query if row (5,'support',35000) is added to mytable. Now there are two rows in 'support' dept that have the same highest salary.

CodePudding user response：

select Id from table as t1 inner join 
    (select id, max(salary) from table  group by dept) as t2 on t1.id= t2.id