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;