From: Charlie <scorpdaddy@hotmail.com>
To: saulo.venancio@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: help on select
Date: Thu, 21 Apr 2011 08:03:27 +0000
Message-ID: <BAY160-w7A8FDEF8094A8A64B6AA4CE920@phx.gbl> (raw)
In-Reply-To: <BANLkTinpuRYSkCkU7ovGojjAhbgXcLAP=g@mail.gmail.com>
References: <BANLkTinpuRYSkCkU7ovGojjAhbgXcLAP=g@mail.gmail.com>
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.venancio@gmail.com
Date: Wed, 20 Apr 2011 17:10:32 -0300
Subject: [SQL] help on select
To: pgsql-sql@postgresql.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.venancio@gmail.com
=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scorpdaddy@hotmail.com, saulo.venancio@gmail.com
Subject: Re: help on select
In-Reply-To: <BAY160-w7A8FDEF8094A8A64B6AA4CE920@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox