sqlite3: my sql query runs into endless loop

Question

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.

Show us some sample table data and the expected result - all as formatted text, not images. (And take a look at minimal reproducible example, make it easy to assist you!) — jarlh
– jarlh, Commented Feb 27, 2020 at 12:51

Samuel Nilsson · Accepted Answer · 2020-02-27 13:07:49Z

1

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.

answered Feb 27, 2020 at 13:07

Samuel Nilsson

112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1