Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id C59541336473 for ; Tue, 21 Sep 2010 05:11:33 -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 08726-07 for ; Tue, 21 Sep 2010 08:11:26 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.go-wipex.it (mx1.go-wipex.it [89.202.235.7]) by mail.postgresql.org (Postfix) with ESMTP id 547BE1335ED4 for ; Tue, 21 Sep 2010 05:11:25 -0300 (ADT) Received: from unipex-local-server (unipex.wipex [172.16.20.247]) by smtp.go-wipex.it (Postfix) with ESMTP id 9FA3610B00B6; Tue, 21 Sep 2010 10:11:23 +0200 (CEST) Received: from [192.168.1.103] (michele [192.168.1.103]) by unipex-local-server (Postfix) with ESMTP id 3CD70560001; Tue, 21 Sep 2010 10:11:23 +0200 (CEST) Message-ID: <4C9868AB.6050200@unipex.it> Date: Tue, 21 Sep 2010 10:11:23 +0200 From: Michele Petrazzo - Unipex User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1b1) Gecko/20060724 Thunderbird/2.0a1 Mnenhy/0.7.4.0 MIME-Version: 1.0 To: Oliveiros d'Azevedo Cristina CC: pgsql-sql@postgresql.org Subject: Re: all the table values equal References: <4C93803A.4080706@unipex.it> <438B32316EF6496D9641819BA16447A2@marktestcr.marktest.pt> <4C938D09.3050105@unipex.it> <81C545E3469E4DF58720C282A469E78A@marktestcr.marktest.pt> In-Reply-To: <81C545E3469E4DF58720C282A469E78A@marktestcr.marktest.pt> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201009/60 X-Sequence-Number: 34591 Oliveiros d'Azevedo Cristina ha scritto: > Hello again, Michele, > Ciao, > I haven't open my mailbox during weekend so I couldn't follow up your > question. > No problem! > It would help if you explain a little better the background of the > problem you're trying to solve. > > You want to find all the user IDs which have the same value on a > given time interval? > > Is my understanding correct? > Yes. Re-reading my post I saw that I could explain better! id_user | value | datetime 1 | 1 | xxx 1 | 2 | xxx+1 1 | -1 | xxx+2 2 | -1 | xxx 2 | -1 | xxx+1 3 | 4 | xxx 3 | 10 | xxx+1 3 | 4 | xxx+2 4 | 3 | xxx 4 | 3 | xxx+1 So, the new question: how I can find which id_user has _all_ the "value" that I'm looking for? Say -1 as 3 and I want a id_user=2 for the first and for the latter id_user=4 Thanks, Michele > Best, Oliver > > ----- Original Message ----- From: "Michele Petrazzo - Unipex" > To: "Oliveiros d'Azevedo Cristina" > Cc: Sent: > Friday, September 17, 2010 4:45 PM Subject: Re: [SQL] all the table > values equal > > >> Oliveiros d'Azevedo Cristina ha scritto: >>> Howdy , Michele, >>> >>> Give this a try >>> >>> SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- >>> plug here beginning of interval AND B -- and end here GROUP BY >>> id_user HAVING COUNT(*) = -SUM(value) >>> >>> Then tell me if it gives you what you want >> >> >> Thanks, it works, but... it's really a trickle that exploits the >> value -1 if I understand how its work. If there is another value >> where look for? Example 13? >> >> Thanks >> >> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To >> make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > >