mankee mankee - 2 months ago 7
SQL Question

SQL Query which returns records which appear X times in table

I have an SQL table responsible for storing instances of user's flagging content. This is my table:

Flagged Content
:

1. instance_id
2. content_id
3. user_id


Now I want to execute a query which returns all the
content_id
which appear at least
3
times within the
flagged content
table. However, I am not sure how to proceed.

Answer

You can get the count using group by:

select content_id
from flagged_content fc 
group by content_id
having count(*) >= 3;

If you want the original records, then use a join:

select fc.*
from flagged_content fc join
     (select content_id
      from flagged_content fc 
      group by content_id
      having count(*) >= 3
     ) c
     on fc.content_id = c.content_id;
Comments