SQL statement for maximum common element in a set

Question

I have a table like

id contact value
1  A       2
2  A       3
3  B       2
4  B       3
5  B       4
6  C       2

Now I would like to get the common maximum value for a given set of contacts. For example: if my contact set was {A,B} it would return 3; for the set {A,C} it would return 2 for the set {B} it would return 4

What SQL statement(s) can do this?

Answer 1

SELECT max(value) FROM table WHERE contact IN ('A', 'C')

Edit: max common

declare @contacts table ( contact nchar(10) )


insert into @contacts values ('a')
insert into @contacts values ('b')


select MAX(value)
from MyTable
where (select COUNT(*) from @contacts) = 
   (select COUNT(*) 
    from MyTable t
    join @contacts c on c.contact = t.contact
    where t.value = MyTable.value)

Answer 2

Try this:

  SELECT value, count(distinct contact) as cnt
    FROM my_table
   WHERE contact IN ('A', 'C')
   GROUP BY value
   HAVING cnt = 2
   ORDER BY value DESC
   LIMIT 1

This is MySQL syntax, may differ for your database. The number (2) in HAVING clause is the number of elements in set.

Answer 3

Most will tell you to use:

  SELECT MAX(t.value)
    FROM TABLE t
   WHERE t.contact IN ('A', 'C')
GROUP BY t.value
  HAVING COUNT(DISTINCT t.*) = 2

Couple of caveats:

• The DISTINCT is key, otherwise you could have two rows of t.contact = 'A'.
• The number of COUNT(DISTINCT t.*) has to equal the number of values specified in the IN clause

My preference is to use JOINs:

  SELECT MAX(t.value)
    FROM TABLE t
    JOIN TABLE t2 ON t2.value = t.value AND t2.contact = 'C'
   WHERE t.contact = 'A'

The downside to this is that you have to do a self join (join to the same table) for every criteria (contact value in this case).