Get rows with maximum count

Question

I need to perform a query to get the candies that are most liked by kids and here's what I've got:

SELECT COUNT(*) as counts,candies.name
FROM candies 
INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
INNER JOIN kids ON kids.id = kid_candy.kid_id
GROUP BY candies.name

which would return:

counts  | name
--------+---------
  3     | snowbear
  3     | whiterabbit
  2     | lollipop

All I want to see would be just

counts  | name
--------+---------
  3     | snowbear
  3     | whiterabbit

So what should my query be?

Dan · Accepted Answer · 2014-10-21 17:14:55Z

3

So, first lets get the counts per candy name (tmp_table), then get the max count of all candies (max_cnt) and finally put it all together and get the candies from tmp_table which have a count equal to max(counts) from max_cnt table...

 with tmp_table AS (
    select COUNT(*) as counts,candies.name as c_name
    from candies 
    INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
    INNER JOIN kids ON kids.id = kid_candy.kid_id
    GROUP BY candies.name        
 ), 
 max_cnt AS (
    SELECT max(counts) as max_count from tmp_table
 )
 SELECT counts, c_name as candies
 FROM tmp_table 
 JOIN max_cnt on max_count = counts

answered Oct 21, 2014 at 17:14

Dan

8765 silver badges15 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:29:55Z

Assuming a classical n:m relationship between kids and candies like detailed here:

How to implement a many-to-many relationship in PostgreSQL?

You should provide such details in your question.

SELECT c.id, kc.counts, c.name
FROM  (
   SELECT candy_id AS id, count(*) AS counts
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   kid_candy
   GROUP  BY 1
   ) kc
JOIN   candies c USING (id)
WHERE  kc.rnk = 1;

This should be considerably faster than a query with two CTEs and needless joins.

Major points

It is potentially wrong (and more expensive) to group by candies.name. The name might not be unique. Use the primary key column for that, which is probably candies.id
Assuming referential integrity we do not need to join to the table kids at all.
Since we need to inspect the whole table, it is faster to aggregate first and join to candies to get the name later.
You can run a window function over an aggregate function:
- Get the distinct sum of a joined table column

Vignesh Kumar A · Accepted Answer · 2014-10-21 17:17:17Z

0

Try this

SELECT * FROM
(
   SELECT COUNT(*) AS Counts,candies.name FROM candies 
     INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
     INNER JOIN kids ON kids.id = kid_candy.kid_id
   GROUP BY candies.name
 ) T1 JOIN
SELECT MAX(counts) MaxCount FROM
(
   SELECT COUNT(*) AS Counts,candies.name FROM candies 
      INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
      INNER JOIN kids ON kids.id = kid_candy.kid_id
   GROUP BY candies.name
) T1 ON T1.counts =T2.MaxCount

answered Oct 21, 2014 at 17:17

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Comments

Edward Manda · Accepted Answer · 2014-10-21 17:19:14Z

0

Do a sub select

Select max(counts) 
    (select COUNT(*) as counts,candies.name from candies 
INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
INNER JOIN kids ON kids.id = kid_candy.kid_id
GROUP BY candies.name)

answered Oct 21, 2014 at 17:19

Edward Manda

5893 silver badges7 bronze badges

1 Comment

Erwin Brandstetter Over a year ago

This would just get a the maximum count, not the desired result, but the syntax is wrong: missing FROM clause, missing table alias.

Collectives™ on Stack Overflow

Get rows with maximum count

4 Answers 4

Comments

Major points

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Major points

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related