How to get the row data with max value field in MySQL?

Question

I just started to learn MySQL. How can I get max reg_count row data in the following data?

    id  title   genreID  countryID  artistID  albumID  reg_count
------  ------  -------  ---------  --------  -------  ---------
    16  hh            2          1         9       35         12
    17  jj            2          1         9       36         34
    18  kk            2          1         9       36         45
    19  ll            2          1        10       33         56
    20  zz            2          1        10       34         99
    21  xx            2          1        10       34         52

I want to get the row data in which id is 18 and 20. These row data have max reg_count value in each artistID field. So I want to get the following row.

    id  title   genreID  countryID  artistID  albumID  reg_count
------  ------  -------  ---------  --------  -------  ---------
    18  kk            2          1         9       36         45
    20  zz            2          1        10       34         99

I tried with "GROUP BY". But I got unwanted results. How to get this ?

Answer 1

You can use aggregation function max( ) for get the max of an artist you could use

select artistID, max(reg_count)
from my_tbale  
group by  artistID

for get the related rows you could use a subquery with the previous query

seect m.* from my_table m
inner join  (
    select artistID, max(reg_count) max_count
    from my_tbale  
    group by  artistID
) t on t.artistID = m.artistID  
    AND t.max_count = m.reg_count