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

Question

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

I used the ranking window functions and CTE to solve this query

with rank_message as (select id,dept,
ROW_NUMBER() over (partition by dept order by salary desc) as sal
from table S)
select id,dept from rank_message where sal=1

Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) You can easily adapt the accepted answer there to order by `salary` instead of the `id` — derpirscher, Dec 22 '21 at 13:18

score 0 · Answer 1 · answered Dec 22 '21 at 15:02

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.

score -2 · Answer 2 · answered Dec 22 '21 at 13:30

-2

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

answered Dec 22 '21 at 13:30

Bhushan

104
1
9

2

Uncomplete grouping. May (will) give incorrect output. – Akina Dec 22 '21 at 13:40

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

2 Answers2