Thread: help on select

Hi guys, 

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, 

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
bydatetime    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