Consecutive without recursion.

[emihir0]

I have the following table:

id | year | team
1 | 2001 | 1
2 | 2003 | 1
3 | 2005 | 2
4 | 2006 | 1
5 | 2008 | 3


6 | 2012 | 1
7 | 2014 | 1

And I want to obtain the following:

id | id
1 | 2
6 | 7

Basically, the touples which are consecutive of team '1'.

I know how to obtain this:

id | id
1 | 2
2 | 4
4 | 6
6 | 7



which are touples of 2 closest teams of '1'.

[ranman256]

your question makes no sense.

[emihir0]

I want consecutive pairs of team '1' in my output (where there is no other data between the pair of 2).

So for example ID 1 and 3 would not qualify, as there is ID 2 between ID1 and 3.

However, ID6 and 7 would qualify.

Please bear in mind that ID field is not autoNum field (I made this arbitrarily easier here as an example).

[June7]

How do you obtain that output?

I suspect will need VBA writing records to a temp table.

[emihir0]

Input table teams:

id | year | team
1 | 2001 | 1
2 | 2003 | 1
3 | 2005 | 2
4 | 2006 | 1
5 | 2008 | 3
6 | 2012 | 1
7 | 2014 | 1


Output:


id | id
1 | 2
2 | 4
4 | 6
6 | 7

Query:

SELECT t3.id, t4.id 
FROM 
        teams t3 
            JOIN teams t4 
                ON t3.team = t4.team AND t3.id <> t4.id
            JOIN (
                SELECT         t1.team, t1.year AS year1, min(t2.year) AS year2
                FROM         teams t1 left JOIN teams t2
                            ON t1.team = t2.team AND t1.year < t2.year 
                WHERE         t1.team = 1 AND t2.team = 1
                GROUP BY     t1.team,t1.year
                ) Q1 
                ON t3.team = q1.team AND t3.year = q1.year1
WHERE t3.year = q1.year1 AND t4.year = q1.year2;

[June7]

Don't think your second desired output can be done solely with SQL. But did you try adding filter criteria to restrict dataset to just the team records?

Did you mean 'tuple'?

[emihir0]

Don't think your second desired output can be done solely with SQL. But did you try adding filter criteria to restrict dataset to just the team records?

Did you mean 'tuple'?

Yes, I meant tuple, sorry.

If you mean the 4 tuple output as my second desired ouput, I can do it though SQL (as I provided in my post above).

However, I cannot figure out how to do the first one, with 2 tuples. Logically it should be way easier... maybe I'm just tired today and should attempt it tomorrow again.

[June7]

Sorry, got your first and second mixed up. Yes, I expect the more restrictive output would be more difficult.