How to obtain the maximum value of IDs in each group in SQL-2

 

 

I have such a table (magazine ):

Id magazinename totalno

1 Spring Dance 2

2 Spring Dance 3

3 Spring Dance 5

4 readers 3

5 readers 2

6 lover 5

7. Life Power 3

8 taste life 9

I want magazinename to have the largest record in totalno. If magazinename does not have the same record, just take the one.

For example, the above result should be

Id magazinename totalno

3 Spring Dance 5

4 readers 3

6 lover 5

7. Life Power 3

8 taste life 9

The answer is as follows:

Select max (totalno) as ABC, ID, magazinename from magazine group by magazinename having count (magazinename)> = 1