CODEWITHSUNDEEP
mysqlsql
Silver0427
Silver0427

Reputation: 63

How to get a certain record after 'group by' operation

Such as I have a table like :

enter image description here

I want to get the highest student from every class. How can I do?

select * 
from (select * 
      from stu 
      order by height desc) 
group by class_id

This doesn't work. Ty

Upvotes: 0

Views: 39

Answers (3)

VBoka
VBoka

Reputation: 9083

select *
from stu s
where height = (select max(height) from stu s1
                where s1.class_id = s.class_id
                group by class_id)

Here is a demo

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You need a subquery for max heigth and join 

select * from stu 
inner join  (
  select class_id , max(height)  max_class_height
  from stu 
  group by  class_id

  ) t on t.class_id = stu.class_id and t.max_class_height = stu.height

Upvotes: 1

Akina
Akina

Reputation: 42632

SELECT t1.* 
FROM stu t1
JOIN ( SELECT class_id, MAX(height) height 
       FROM stu 
       GROUP BY class_id ) t2 USING (class_id, height)

If more than one student have the same maximal height over a class then all of them will be returned.

Upvotes: 1

Related Questions