SQL find rows that have adjacent number

4295 views sql
0

I have a table that looks more or less like this:

+----------+-------+
| position | group |
+----------+-------+
| 1        | a     |
+----------+-------+
| 5        | b     |
+----------+-------+
| 6        | b     |
+----------+-------+
| 7        | c     |
+----------+-------+
| 8        | b     |
+----------+-------+

I want to SELECT combinations of rows that have adjacent positions within the same group. For example, given the table above, the output of the query should be:

+----------+-------+
| position | group |
+----------+-------+
| 5        | b     |
+----------+-------+
| 6        | b     |
+----------+-------+

Performance is a bit of an issue because the table has 1.5 billion rows, but position and group are both indexed so it's relatively quick. Any suggestions on how to write this query? I'm not sure where to start because I don't know how to write a WHERE statement that involves multiple rows of output.

answered question

2 Answers

3

select distinct T1.position, T2.group from
T as T1
inner join
T as T2
on(T1.group=T2.group and t2.position=t1.position+1)

posted this
13

I would use subselect like this:

select * from mytable m1
where (select count(*) from mytable m2 where m2.position = m1.position + 1 and m2.group = m1.group) > 0

posted this

Have an answer?

JD

Please login first before posting an answer.