I'm not sure, but try to do the group by on the round
On 2 Dec 2014 03:57, "Jonas Goerlich"
Hello,
I am using MonetDB in a university project dealing with analysis of twitter data.
"stuff" contains some pre-selected user ids. "tweets1207" contain tweet data for one day (userid, latitude, longitude, time, ....)
What I want in the end is to find all (rounded) unique geo-coordinates of tweets by users in "stuff". I am using the following query:
SELECT userid, ROUND(latitude,0) AS lat, ROUND(longitude,0) AS long FROM tweets1207 INNER JOIN stuff USING (userid) WHERE latitude <> 0 AND longitude <> 0 GROUP BY userid, lat, long ORDER BY userid, lat, long;
The result set however still contains rows with duplicate values for "lat" and "long", e.g.
userid lat long ----------------------------- 19703 41 -74 19703 41 -74
If I remove the INNER JOIN part from the query, the grouping works as expected.
To check whether I am doing something fundamentally wrong (don't have much experience with SQL), I tried running the same query in a sqlite version of the data, where it produced the result I expected (although much, much slower...)
Can anybody point me in the right direction? Am I doing something wrong here?
Best regards and thanks in advance, Jonas
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list