-1

How to get the total row data that has max(reg_count). When I try this, it gives me only max reg_count value. I want to get the whole row data that has max reg_count in every albumID.(So like this: 4 Bemisal ha 1 1 8) - total 4 rows

SELECT albumID, max(reg_count) as max_count
FROM contentnew 
GROUP BY albumID

enter image description here

Please help me!

The Impaler
  • 45,731
  • 9
  • 39
  • 76
Jin youcheng
  • 96
  • 2
  • 9

1 Answers1

2

You don't mention the version of MySQL you are using so I'll assume it's a modern one (8.x). You can use the ROW_NUMBER() window function to identify the row you need.

For example:

select *
from (
  select *,
    row_number() over(partition by albumID order by reg_count desc) as rn
  from contentnew
) x
where rn = 1

In MySQL 5.x you can use correlated subquery:

select *
from contentnew a
where a.reg_count = (
  select max(reg_count) 
  from contentnew b 
  where b.albumID = a.albumID)
)
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Thank you very much. I used your second answer and it worked well. I am a beginner in MySQL. Where can I see the MySQL version? (First answer worked well also, but I am familiar to the second answer, so I used it) – Jin youcheng Apr 28 '20 at 19:27
  • @Jinyoucheng If the first query worked, then you must be in 8.x. Anyway, you can get the version of MySQL with `SELECT VERSION();`. – The Impaler Apr 28 '20 at 19:52
  • Oh, Thank you for your help. (Mine was 10.4.11-MariaDB) – Jin youcheng Apr 28 '20 at 19:59
  • @Jinyoucheng Just a note. For clarity I changed the tag of your question to MariaDB. Even though these two databases were compatible years back, they are now diverging quite a bit. Your solution may work in one and not the other. – The Impaler Apr 28 '20 at 20:04