Howdy, Michele. Any news? Any progress/problems? If you want to find out all the id_users who don't change value in a given time interval you can use something like this.
SELECT "id_user","value" FROM ( SELECT "id_user", COUNT(*) as total FROM t_yourtable WHERE "datetime" BETWEEN '2010-9-1' -- Interval beginning AND '2010-9-11' -- interval end GROUP BY "id_user" ) as foo NATURAL JOIN ( SELECT "id_user","value",COUNT(*) as total FROM t_yourtable WHERE "datetime" BETWEEN '2010-9-1' -- again, plug interval beginning here AND '2010-9-11' -- and interval end here GROUP BY "id_user","value" ) as bar This will give you the users who didn't change value on the time interval. Finally, if you want to look for a specific value just add the clause "WHERE "value" = x -- x=the specific value you want to look for to the query above. It'll return just the id_user(s) that have just the "value" x. Please try it out and feel free to get back to me if it didn't work or if it didn't solve your problem Best, Oliver ----- Original Message ----- From: "Oliveiros d'Azevedo Cristina" <oliveiros.crist...@marktest.pt> To: "Michele Petrazzo - Unipex" <michele.petra...@unipex.it> Cc: <pgsql-sql@postgresql.org> Sent: Tuesday, September 21, 2010 11:25 AM Subject: Re: [SQL] all the table values equal > >>> 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 >> > > OK, So, analysing your new question, the time interval is still important? > Or no longer? > So writting your query in english : "I want all users that don't change > "value" and their value is equal to x ?" > > Or will you need to find all the users that don't change value with just one > query? And the query would return something like > > id_user | value > 2 | -1 > 4| 3 > > ? > Please advice me on this. > > I am just trying to avoid misunderstanding your requirements and ending up > with a query that doesn't really do what you need. > > Best, > Oliver > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
