How to get dependent data using sql query

Asked Feb 10 '20 at 14:40

Active Feb 10 '20 at 14:46

Viewed 34 times

I am trying to get the Latest Grade and highest Marks scored in that particular Grade by each student. I do get Latest Grade by using Max function but struggle to get Maximum marks for that Grade. Would really appreciate some thoughts on this...

This is original data

+------+-----+-----+
| S_id |Grade|Marks|
+------+-----+-----+
| A1   |   1 |  10 |
| B1   |   2 |  30 |
| B2   |   2 |  20 |
| B2   |   2 |  40 |
| B2   |   3 |  50 |
| B2   |   3 |  20 |
| B3   |   1 |  30 |
| B3   |   1 |  40 |
| B4   |   1 |  30 |
| B4   |   2 |  20 |
+------+-----+-----+

This is the expected result

+----------------------+------+-----+
| S_id                 | Grade|Marks| 
+----------------------+------+-----+
| A1                   |    1 |  10 |
| B1                   |    2 |  30 |
| B2                   |    3 |  20 |
| B3                   |    1 |  40 |
| B4                   |    2 |  20 |
+----------------------+------+-----+

edited Feb 10 '20 at 14:46

mitkosoft

5,262
1
13
31

asked Feb 10 '20 at 14:40

J Parle

This help you? https://stackoverflow.com/questions/11434124/mysql-select-distinct-by-highest-value – half of a glazier Feb 10 '20 at 14:45
Thank you for editing mitkosoft – J Parle Feb 10 '20 at 14:48
Your table appears to have no PRIMARY KEY. This is likely to prove problematic further down the road. – Strawberry Feb 10 '20 at 14:48
What version of MySQL are you using? Also, if you want the "Latest Grade and highest Marks", wouldn't you expect the mark of 50 to be returned for student B2? – GarethD Feb 10 '20 at 14:52
Sorry, my bad. B2 should have 50. Using MySql 8 – J Parle Feb 10 '20 at 15:03

How to get dependent data using sql query

0 Answers0