max count together in an sql query 2

Question

This is referring to the question i asked before, and got a really quick answaer (http://stackoverflow.com/questions/751156/max-count-together-in-an-sql-query). The Problemset is similar, but the solution in the prevous question would force me to access a db in a loop which will cause performance problems. So what i have now, after some joins is:

    id | description
     0 | bla
     0 | blub
     0 | bla
     1 | blablub
     1 | bla
   ... | ...

As u can see, now the id is not a primary key anymore. What i want is to get the most used description for each id in the resultset. It should look something like this:

 id | most_popular_description | times_the_desc_appeared_for_an_id
  0 |                      bla |                                 2
  1 |                  blablub |                                 1
... |                      ... |                               ...

Answer 1

This should do the trick.

select id, description, COUNT(description)
from mytable
group by id, description
order by 3 desc

Answer 2

If you only want the most popular items, then I believe this should give you the result set you're looking for. There are other ways of doing this, but stats_mode is the easiest way to obtain the "most prevalent" value in a group (i.e. the Mode).

SELECT t.id,
       t.description AS most_popular_description,
       COUNT(*) AS times_the_desc_appeared_for_an_id
FROM mytable t INNER JOIN (
  SELECT id, stats_mode(description) AS desc FROM mytable GROUP BY id
) a ON t.id = a.id AND t.description = a.desc
GROUP BY t.id, t.description;

Note that the nested query (inline view) is necessary since you also want the count.

Answer 3

I think you can use the dense_rank() analytic function to get the top N for each group set.

Something like this:

select id, description, times_the_desc_appeared_for_an_id
from
(
  select id, description, count(description) times_the_desc_appeared_for_an_id
  dense_rank() over (partition by id, description order by count(description) desc) position
  from mytable
  group by id, description
)
where
  position <= 3
order by id, times_the_desc_appeared_for_an_id;