CODEWITHSUNDEEP
mysql
Samik Chattopadhyay
Samik Chattopadhyay

Reputation: 1860

How can I get common value from sets using MySQL queries?

I have a table with ID, AID and Year field. How can I find the latest common year among the sets (common AIDs makes a set)

ID  AID Year
---------
1   1   2001
2   1   2002
3   1   2003
4   1   2004

5   2   2003
6   2   2004
7   2   2005

8   3   2004
9   3   2005
10  3   2006

Latest common year 2004

Upvotes: 0

Views: 59

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

I believe this is the most easy way of doing it.

Query

    SELECT 
      Year
    FROM 
     t
    GROUP BY 
     Year
    HAVING 
     COUNT(*) = (SELECT COUNT(DISTINCT t.AID) FROM t)

Result

    | Year |
    | ---- |
    | 2004 |

see demo

This query makes it more visible why it works

Query

SELECT 
     Year
  ,  COUNT(*) AS count_per_AID
  , (SELECT COUNT(DISTINCT t.AID) FROM t) AS unique_count_per_AID
  , (COUNT(*) = (SELECT COUNT(DISTINCT t.AID) FROM t)) AS most_common_value
FROM 
 t
GROUP BY 
 Year

Result

| Year | count_per_AID  | unique_count_per_AID | most_common_value |
| ---- | -------------- | -------------------- | ----------------- |
| 2001 | 1              | 3                    | 0                 |
| 2002 | 1              | 3                    | 0                 |
| 2003 | 2              | 3                    | 0                 |
| 2004 | 3              | 3                    | 1                 |
| 2005 | 2              | 3                    | 0                 |
| 2006 | 1              | 3                    | 0                 |

This query returns all the common years and not the latest common year.

Indeed forpas thanks for making that comment.
The fix would be to use the query 

SELECT 
  Year
FROM 
 t
GROUP BY 
 Year
HAVING 
 COUNT(*) = (SELECT COUNT(DISTINCT t.AID) FROM t)
ORDER BY 
 t.Year DESC
LIMIT 1
;

see demo

Upvotes: 2

Related Questions