Complex sort and grouping with mysql

I have the ff table:

---------------------------
ID | ChapterNo | HitCount |  
---------------------------
1  |    2      |   1000   |
2  |    2      |   2000   |
3  |    1      |   3000   |
4  |    3      |   1000   |
5  |    1      |   3500   |
---------------------------

Basically I need to archive this result:

Get all the unique chapterno where each have the highest hit count and then order by chapterno descending

ID | ChapterNo | HitCount |  
---------------------------
4  |    3      |   1000   |
2  |    2      |   2000   |
5  |    1      |   3500   |
------开发者_运维知识库---------------------

I tried the ff. query:

SELECT t1.*, Max(t1.hitcount) AS maxhit
FROM chapter as t1
GROUP BY t1.chapterno
ORDER BY t1.chapterno DESC

But some how it doesnt return the one with highest hitcount.

How can I fix this?

Thank you

SELECT t1.*, t1.hitcount AS maxhit
FROM chapter as t1
WHERE t1.hitcount = (select max(hitcount) from chapter where chapterno = t1.chapterno)
ORDER BY t1.chapterno DESC

SELECT t1.id, t1.chapterno, t2.maxhits
FROM chapter as t1, 
    (SELECT id, chapterno, Max(hitcount) AS maxhits
     FROM chapter 
     GROUP BY chapterno) AS t2
WHERE t2.chapterno = t1.chapterno
AND t1.hitcount = t2.maxhits
ORDER BY t1.chapterno DESC

Try this one -

SELECT c1.id, c1.ChapterNo, c1.HitCount FROM chapter c1
JOIN (SELECT ChapterNo, MAX(HitCount) max_hitCount
      FROM chapter
      GROUP BY ChapterNo) c2
ON c1.ChapterNo = c2.ChapterNo AND c1.HitCount = c2.max_hitCount
ORDER BY c1.ChapterNo DESC;

SELECT t1.*, t1.hitcount AS maxhit
FROM chapter as t1
WHERE t1.hitcount = (
    SELECT MAX t1.hitcount
    from chapter as t2
    where t2.ChapterNo = t1.chapterNo
)
ORDER BY t1.chapterno DESC

This uses a correlated subquery, which can become unefficient. Another possibility is to use an uncorrelated query in the from or left join.

More info on this article

although all above answers are perfect, i think it also can be done using SELF JOIN

SELECT *
FROM chapter ch
WHERE (
   SELECT COUNT(*) FROM chapter ch2
   WHERE ch2.chapterno = ch.chapterno and ch2.hitcount > ch.hitcount
) <= 2;