sqlite3: my sql query runs into endless loop

user_3930618 0
Viewed 64

I have the following table:

id as int, prop as text, timestamp as int, json as blob

I want to find all pairs, which have the same prop and with the same timestamp. Later I want to extend the timestamp to e.g., +/- 5 sec.

I try to do it with INNER JOIN but my query runs into endless loop: 

SELECT * FROM myTable c 
INNER JOIN myTable c1 
     ON c.id != c1.id 
     AND c.prop = c1.prop 
     AND c.timestamp = c1.timestamp

Maybe my approach is wrong. What is the problem with my query? How can I do it? Actually, I need groups with these pairs.

sqlite sql python
1 Answers

You could try to see if the query gets faster with a GROUP BY:

SELECT * FROM myTable
WHERE (prop, timestamp) IN (
    SELECT prop, timestamp
    FROM myTable
    GROUP BY prop, timestamp
    HAVING COUNT(*) > 1
)

Although its hard to say without sample data.

If the table is huge you might have to create an index to speed up the query.

user_53648030
Related