GROUP BY with JOIN produces unexpected results

2 Dec 2014

      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

Jonas Goerlich

amihay gonen

Jonas Goerlich

tags

participants (2)