pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp on select
3+ messages / 3 participants
[nested] [flat]
* help on select
@ 2011-04-20 20:10 Saulo Venâncio <saulo.venancio@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Saulo Venâncio @ 2011-04-20 20:10 UTC (permalink / raw)
To: pgsql-sql
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
*
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: help on select
@ 2011-04-21 08:03 Charlie <scorpdaddy@hotmail.com>
parent: Saulo Venâncio <saulo.venancio@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Charlie @ 2011-04-21 08:03 UTC (permalink / raw)
To: saulo.venancio@gmail.com; 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.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
=
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: help on select
@ 2011-04-21 23:15 Jasen Betts <jasen@xnet.co.nz>
parent: Saulo Venâncio <saulo.venancio@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jasen Betts @ 2011-04-21 23:15 UTC (permalink / raw)
To: pgsql-sql
On 2011-04-20, Saulo Venâncio <saulo.venancio@gmail.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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-04-21 23:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-20 20:10 help on select Saulo Venâncio <saulo.venancio@gmail.com>
2011-04-21 08:03 ` Charlie <scorpdaddy@hotmail.com>
2011-04-21 23:15 ` Jasen Betts <jasen@xnet.co.nz>
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