Re: help on select

You might try:

WITH
pontos AS
(
SELECT column1 AS idponto
FROM (VALUES (10), (11), (23), (24) ) AS a
),
subset AS
(
SELECT b.idponto, date_trunc('day', datetime) AS datetime
FROM medidas b
INNER JOIN pontos USING(idponto)
GROUP BY b.idponto, date_trunc('day', datetime)
),
datetimes AS
(
SELECT datetime
FROM subset
GROUP BY datetime
HAVING COUNT(*) = (SELECT COUNT(*) FROM pontos)
)
SELECT max(datetime)
FROM datetimes
;

From: saulo(dot)venancio(at)gmail(dot)com
Date: Wed, 20 Apr 2011 17:10:32 -0300
Subject: [SQL] help on select
To: pgsql-sql(at)postgresql(dot)org

Hi guys,
I need your help.
I have a table called medidas, in this table i have some ocurrences that has id_medida(primary key) id_ponto (sec_key) and also datetime field as timestamp.
i would like to know from a set of idpontos, e.g. 10,11,23,24.... how can i get the most recent date that is common to all??
for example, if idponto das date 2011-02-03 but none of others have this date in the db i dont want this. i want one common for all..
thanks.

-----------------------
Saulo B. M. Venâncio
Control and Automation Eng. Student
Associate in Business Management
T +55 4884121486
E saulo(dot)venancio(at)gmail(dot)com