Geotob Geotob - 4 months ago 10
SQL Question

Select largest date common to mutliple rows

Given a table like below, with a

UNIQUE CONSTRAINT(user, date)
, how do I select the
max(date)
common to all users in a list
(1, 2)
?

ID | date | user | value
-----------------------------
1 | 2016-5-1 | 1 | a
2 | 2016-5-1 | 2 | b
3 | 2016-5-3 | 2 | c
4 | 2015-5-4 | 1 | d


So with
WHERE user IN (1, 2)
, the query should return
2016-5-1
.

I have two working versions below so far, but neither seems really clean. Are there any better/more generic/more flexible ways to achieve what I want? Note I only need this to work in PostgreSQL.

a) relying on the length of the
sensor
list:

SELECT a.date FROM
(SELECT date, COUNT(date)
FROM mytable WHERE sensor_id IN (8, 9)
GROUP BY date ORDER BY date DESC) a
WHERE a.count = 2 LIMIT 1;


b) with separate subqueries:

SELECT i.date
FROM (SELECT date FROM mytable WHERE user_id=1) i
JOIN (SELECT date FROM mytable WHERE user_id=2) j
ON i.date = j.date ORDER BY date DESC LIMIT 1;

Answer
select date
from mytable
where user_id in (1,2)
group by date
having count(date) = 2
order by date desc
limit 1;

A number in having must be equal to a cardinality of list in where.