get max value of each column group by student ID-CodePudding

I have a table with columns like this

Std_id  Class Section
14        8      2
14        8      1
14        9      1
15        8      3
15        8      2
15        8      1

Now I want student max class and max section by unique Student ID Like this

Std_id  Class Section
14        9      1
15        8      3

I tried following queries with no success

SELECT std_id, class, section FROM `session` GROUP BY std_id ORDER BY std_id ASC,class DESC,section DESC

And

SELECT DISTINCT student_id, class_id, section_id FROM `student_session` ORDER BY student_id ASC,class_id DESC,section_id DESC

CodePudding user response：

Try out this query

SELECT std_id, MAX(class),MAX(section) FROM student GROUP BY std_id

CodePudding user response：

It should help:

SELECT Std_id, MAX(Class) as max_class, MAX(Section) as max_section 
FROM student 
GROUP BY Std_id

CodePudding user response：

For MySQL 8 :

with cte as 
(
      select *, row_number() over(partition by std_id order by class desc,section desc ) as row_num
      from session 
 )  select std_id,class,section 
    from cte
    where row_num=1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3632cb770098c994936d02849608c586