From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: help on select |
Date: | 2011-04-21 23:15:26 |
Message-ID: | ioqdqe$lb0$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 2011-04-20, Saulo Venâncio <saulo(dot)venancio(at)gmail(dot)com> wrote:
> --bcaec52e65e9b2f22304a15f3840
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> Hi guys,
> I need your help.
> I have a table called medidas, in this table i have some ocurrences that ha=
> s
> 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.
the trick seems to be to GROUP BY datetime
and to use a HAVING clause to reject the unwanted groups using
count(distinct()) to ensure coverage of the list.
-- a table
create temp table medidas(id_medida serial,id_ponto integer,datetime timestamp);
-- some test data.
insert into medidas (id_ponto,datetime) select floor(random()*30+1),('today'::timestamp +
floor(generate_series(0,100000)/10)*'1s'::interval);
-- the query:
-- note you need to paste the list of number in two different places
-- in the query, postgres only counts the length once.
select datetime
from medidas
where id_ponto in (10,11,23,24,27)
group by datetime
having count(distinct(id_ponto)) = array_length( array[10,11,23,24,27],1)
order by datetime desc limit 1;
-- confirmation
select * from medidas where datetime = (
select datetime
from medidas
where id_ponto in (10,11,23,24,27)
group by datetime
having count(distinct(id_ponto)) = array_length(array[10,11,23,24,27],1)
order by datetime desc limit 1
)
order by id_ponto;
what's this for?
Are you looking at keno results to see how recently your pick would have won?
--
⚂⚃ 100% natural
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Earl | 2011-04-28 06:00:40 | Select For Update and Left Outer Join |
Previous Message | Tom Lane | 2011-04-21 14:34:34 | Re: |