CODEWITHSUNDEEP
sqliterows
mssqlw
mssqlw

Reputation: 73

find values which are the same as value above in sqlite

I am trying to find values which are the same as at least two values above it. Please take a look.

id       number
1        2
2        6
3        7
4        7
5        7
6        1
7        2
8        4
9        7

So in this case select would return:

ID      NUMBER
3        7
4        7
5        7

Upvotes: 0

Views: 68

Answers (1)

CL.
CL.

Reputation: 180080

You can look up values in othe rows with a correlated subquery:

SELECT *
FROM MyTable
WHERE number = (SELECT number
                FROM MyTable AS T2
                WHERE T2.id = MyTable.id - 1)
  AND number = (SELECT number
                FROM MyTable AS T2
                WHERE T2.id = MyTable.id - 2);

Upvotes: 1

Related Questions