CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsgreatest-n-per-group
ronzohan
ronzohan

Reputation: 105

Get rows with maximum count

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?

Upvotes: 2

Views: 157

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656882

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

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:

Upvotes: 1

Edward Manda
Edward Manda

Reputation: 579

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)

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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

Upvotes: 0

Dan
Dan

Reputation: 876

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

Upvotes: 3

Related Questions