Complex Sort And Grouping With Mysql

I have the ff table: --------------------------- ID | ChapterNo | HitCount | --------------------------- 1 | 2 | 1000 | 2 | 2 | 2000 | 3 | 1 |

Solution 1:

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

Solution 2:

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

Solution 3:

Try this one -

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

Solution 4:

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
)
ORDERBY 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

Solution 5:

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

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