Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E991D1337BD5 for ; Thu, 21 Apr 2011 05:03:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 52436-02 for ; Thu, 21 Apr 2011 08:03:28 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from bay0-omc4-s26.bay0.hotmail.com (bay0-omc4-s26.bay0.hotmail.com [65.54.190.228]) by mail.postgresql.org (Postfix) with ESMTP id 64D491337B9C for ; Thu, 21 Apr 2011 05:03:28 -0300 (ADT) Received: from BAY160-W7 ([65.54.190.199]) by bay0-omc4-s26.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Thu, 21 Apr 2011 01:03:27 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_987a0766-d30b-45bc-bdbe-70a0e32f0b12_" X-Originating-IP: [142.166.23.178] From: Charlie To: , Subject: Re: help on select Date: Thu, 21 Apr 2011 08:03:27 +0000 Importance: Normal In-Reply-To: References: MIME-Version: 1.0 X-OriginalArrivalTime: 21 Apr 2011 08:03:27.0729 (UTC) FILETIME=[98996E10:01CBFFFA] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.022 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, FREEMAIL_REPLY=1.929, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/33 X-Sequence-Number: 35280 --_987a0766-d30b-45bc-bdbe-70a0e32f0b12_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable You might try: =20 WITH pontos AS ( SELECT column1 AS idponto FROM (VALUES (10)=2C (11)=2C (23)=2C (24) ) AS a )=2C subset AS ( SELECT b.idponto=2C date_trunc('day'=2C datetime) AS datetime FROM medidas b INNER JOIN pontos USING(idponto) GROUP BY b.idponto=2C date_trunc('day'=2C datetime) )=2C datetimes AS ( SELECT datetime FROM subset GROUP BY datetime HAVING COUNT(*) =3D (SELECT COUNT(*) FROM pontos) ) SELECT max(datetime) FROM datetimes =3B =20 =20 From: saulo.venancio@gmail.com Date: Wed=2C 20 Apr 2011 17:10:32 -0300 Subject: [SQL] help on select To: pgsql-sql@postgresql.org Hi guys=2C=20 I need your help. I have a table called medidas=2C in this table i have some ocurrences that = has id_medida(primary key) id_ponto (sec_key) and also datetime field as ti= mestamp. i would like to know from a set of idpontos=2C e.g. 10=2C11=2C23=2C24.... h= ow can i get the most recent date that is common to all?? for example=2C 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=E2ncio Control and Automation Eng. Student Associate in Business Management T +55 4884121486 E saulo.venancio@gmail.com = --_987a0766-d30b-45bc-bdbe-70a0e32f0b12_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable You might try:
=3B
WITH
=3B pontos AS
=3B (
=3B =3B =3B SELECT column1= AS idponto
=3B =3B =3B FROM (VALUES (10)=2C (11)=2C (23)= =2C (24) ) AS a
=3B )=2C
=3B subset AS
=3B (
=3B =3B =3B SELECT b.idpon= to=2C date_trunc('day'=2C datetime) AS datetime
=3B =3B =3B= FROM medidas b
=3B =3B =3B INNER JOIN pontos USING(idponto= )
=3B =3B =3B GROUP BY b.idponto=2C date_trunc('day'=2C dat= etime)
=3B )=2C
=3B datetimes AS
=3B (
=3B =3B =3B SELECT date= time
=3B =3B =3B FROM subset
=3B =3B =3B GR= OUP BY datetime
=3B =3B =3B HAVING COUNT(*) =3D (SELECT COU= NT(*) FROM pontos)
=3B )
SELECT max(datetime)
FROM datetimes
=3B

=3B

=3B

From: saulo.venancio@gmail.com
Date: Wed=2C 20 Apr 2011 17:10:32 -0300Subject: [SQL] help on select
To: pgsql-sql@postgresql.org

Hi g= uys=2C =3B

I need your help.

I have a table called medidas=2C 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=2C e.g. 10=2C11=2C23=2C24.= ... how can i get the most recent date that is common to all??

for example=2C 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=E2ncio

Control and Automation Eng. Student
Associate in Business Management
T +55 4884121486
E saulo.venancio@gma= il.com

= --_987a0766-d30b-45bc-bdbe-70a0e32f0b12_--