From: | Charlie <scorpdaddy(at)hotmail(dot)com> |
---|---|
To: | <saulo(dot)venancio(at)gmail(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: help on select |
Date: | 2011-04-21 08:03:27 |
Message-ID: | BAY160-w7A8FDEF8094A8A64B6AA4CE920@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-04-21 14:34:34 | Re: |
Previous Message | Saulo Venâncio | 2011-04-20 20:10:32 | help on select |