Complex sort and grouping with mysql

Question

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

StevieG · Accepted Answer · 2011-07-26 08:28:56Z

4

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

answered Jul 26, 2011 at 8:28

StevieG

8,7391 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DucDigital Over a year ago

This was so similar to one of my previous query. but i did not think of the where part in this subquery. Thank you @StevieG

Jacob · Accepted Answer · 2011-07-26 08:29:07Z

2

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

answered Jul 26, 2011 at 8:29

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

Comments

Devart · Accepted Answer · 2011-07-26 08:32:45Z

1

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;

answered Jul 26, 2011 at 8:32

Devart

120k23 gold badges172 silver badges189 bronze badges

Comments

Steven Ryssaert · Accepted Answer · 2011-07-26 08:33:27Z

1

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.

Comments

xkeshav · Accepted Answer · 2011-07-26 08:37:20Z

1

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;

answered Jul 26, 2011 at 8:37

xkeshav

54.2k47 gold badges183 silver badges255 bronze badges

Collectives™ on Stack Overflow

Complex sort and grouping with mysql

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related