Can this be done with single SQL query

I'm using MySQL. I have a table

Type SubType
1 1
1 5
1 6
1 8
2 2
2 3
3 1
3 2
3 3

For each type there is some number of subtypes. For every subtype in a type there is a corresponding subtype in the next type:

(1,1) => (2,2)开发者_C百科
(1,5) => (2,3)
(1,6) => (2,2)
(1,8) => (2,3)
(2,2) => (3,1)
(2,3) => (3,2)

In case you haven't seen the pattern, here it is: you sort both current and next types by subtype, then in the next type you get subtype in the same position as your current subtype in current type is. If there are more subtypes in the current type that in the next one, you warp around and start from the first subtype in the next type.

Is it possible to construct a query that takes current type and subtype and returns corresponding subtype in the next type?

---

not sure about mysql... in oracle there are LEAD and LAG functions, as well as CONNECT BY.

I'm a bit confused by your description i must say :)

---

There is probably some way to do this with a query, but I think you it would be a better idea to build a data structure from the table then do your lookups in there. The MySQL queries I'm thinking about would involve getting counts from subqueries, modulo arithmetic, etc. The end result would be a messy query! If instead you had something like this:

In rudimentary Python:

>>> x = [None, [1, 5, 6, 8], [2, 3], [1, 2, 3]]
>>> for i in range(1,3):
...   for j in range(len(x[i])):
...     k = j % len(x[i+1])
...     print(i,x[i][j],i+1,x[i+1][k])
... 
1 1 2 2
1 5 2 3
1 6 2 2
1 8 2 3
2 2 3 1
2 3 3 2