0

Let' say we have this dataset:

import sqlite3
db = sqlite3.connect(':memory:')
c = db.cursor()
c.execute('CREATE TABLE mytable (id integer, description text)')
c.execute('INSERT INTO mytable VALUES (2, "abc")')
c.execute('INSERT INTO mytable VALUES (5, "def")')
c.execute('INSERT INTO mytable VALUES (18, "geh")')
c.execute('INSERT INTO mytable VALUES (19, "ijk")')
c.execute('INSERT INTO mytable VALUES (27, "lmn")')

How to find pairs / couples of rows for which their ID is distant of at most 3? i.e. it should return the rows (2,5) and (18, 19), and maybe also (5,2), (19,18) (but these two last ones are not needed).

I tried:

c.execute('SELECT id as id1 FROM mytable, SELECT id as id2 FROM mytable WHERE abs(id1 - id2) <= 3')

but it does not seem to work:

sqlite3.OperationalError: near "SELECT": syntax error

Basj
  • 41,386
  • 99
  • 383
  • 673

2 Answers2

2

Try this:

SELECT mt1.*, mt2.* FROM mytable mt1, mytable mt2 WHERE abs(mt1.id - mt2.id) <= 3 and mt1.id<mt2.id

Output: [(2, u'abc', 5, u'def'), (18, u'geh', 19, u'ijk')]

The condition mt1.id<mt2.id is used to remove the duplicates.

Basj
  • 41,386
  • 99
  • 383
  • 673
Mark
  • 418
  • 3
  • 12
  • Thanks a lot, it works! Last thing: how to get a list of the rows that match the condition? e.g. `[(2, "abc"), (5, "def"), (18, "geh"), (19, "ijk")]` (with this representation we know that each even element's neighbour is the second element of the pair) – Basj Apr 16 '18 at 10:28
  • you are welcome :-) instead of SELECT mt1.id, mt2.id you can write SELECT mt1.*, mt2.* – Mark Apr 16 '18 at 10:31
1
SELECT t1.id, t2.id 
FROM mytable t1 
CROSS JOIN mytable t2
WHERE t1.id != t2.id AND abs(t1.id - t2.id) <= 3

Output:
[(2, 5), (5, 2), (18, 19), (19, 18)]

Ivan Vinogradov
  • 4,269
  • 6
  • 29
  • 39
  • Thanks! What is the difference between `CROSS JOIN` and `,` as in [this answer](https://stackoverflow.com/a/49854984/1422096)? – Basj Apr 16 '18 at 10:30
  • If you need text as well - do `SELECT t1.id, t1.description, t2.id, t2.description FROM ...` – Ivan Vinogradov Apr 16 '18 at 10:32
  • Basically `CROSS JOIN` and `... FROM t1, t2` do the same thing. I like explicit `JOIN`s because I think it's just a more readable option. You can read about this difference here - https://stackoverflow.com/questions/13476029/multiple-table-select-vs-join-performance – Ivan Vinogradov Apr 16 '18 at 10:37