Getting next row using SQL

Question

I have 1 table with data thus:

Col1      Col2  
------- --------  
Admin001  A  
Admin001  B  
Admin002  C   
Admin002  C  
Admin003  A  
Admin003  C

I need to find all instances of Col2 values with 'A' immediately followed by 'B'. 'A' followed by any other symbol does not count. Is there a way to use SQL to accomplish this?
Environment is DB2 LUW v9.5

Update: How can I do this if I make the table like below?

Col1    Col2      Col3  
----    ------- --------  
1      Admin001     A  
2      Admin002     C   
3      Admin002     C  
4      Admin003     A  
5      Admin003     C
6      Admin001     B 
7      Admin001     A
8      Admin001     C
9      Admin001     B  

Answer 1

How are you sorting the columns? If you aren't sorting them, you could get different results each time, as sometimes A would follow B, and sometimes B would follow A. If you are sorting them, you may be able to use an 'exists' test with the sorting expression.

There is no general method of getting the next (or previous) row in SQL, but many implementations provide their own built-in functions to help with that kind of thing. Unfortunately I'm not familiar with DB2.

Answer 2

Given that there is no implicit ordering of a set, then no, there isn't any reliable way to do this. Your data will need to be ordered (perhaps by a third column, or by column 1) for this to make any sense.

Answer 3

It looks like you're trying to find out who's grade dropped from A to B, so we'll also assume that you want the results where B follows A for the same admin.

SELECT DISTINCT t1.Col2
FROM table t1
INNER JOIN table t2 ON t1.Col2 = t2.Col2
LEFT OUTER JOIN table t3 ON t1.Col2 = t3.Col2
            AND t3.Col1 < t2.Col1 AND t3.Col1 > t1.Col1
WHERE t1.Col3 = 'A'
  AND t2.Col3 = 'B' AND t2.Col1 > t1.Col1
  AND t3.Col1 IS NULL

This yields any admin who has 'A' followed by 'B'.

The INNER JOIN and the first two expressions in the WHERE clause finds all records where 'B' occurs after 'A'. The left OUTER join and the last expression in the WHERE clause finds all records where there are grades between the A and B, and only takes the records without.

You asked to get these results, one per row, like this:

Col1    Col2      Col3  
----    ------- --------  
1      Admin001     A  
6      Admin001     B

I'm going to adapt the above query the easy way.

I'll simply get the A records, get the B records, and union them:

(SELECT t1.Col1, t1.Col2, t1.Col3
FROM table t1
INNER JOIN table t2 ON t1.Col2 = t2.Col2
LEFT OUTER JOIN table t3 ON t1.Col2 = t3.Col2
            AND t3.Col1 < t2.Col1 AND t3.Col1 > t1.Col1
WHERE t1.Col3 = 'A'
  AND t2.Col3 = 'B' AND t2.Col1 > t1.Col1
  AND t3.Col1 IS NULL)
UNION
(SELECT t2.Col1, t2.Col2, t2.Col3
FROM table t1
INNER JOIN table t2 ON t1.Col2 = t2.Col2
LEFT OUTER JOIN table t3 ON t1.Col2 = t3.Col2
            AND t3.Col1 < t2.Col1 AND t3.Col1 > t1.Col1
WHERE t1.Col3 = 'A'
  AND t2.Col3 = 'B' AND t2.Col1 > t1.Col1
  AND t3.Col1 IS NULL)
ORDER BY Col2, Col1

Notice that we're ordering by Col2 first, then Col1. You may also get more than one set of records for each user.

Answer 4

 SELECT DISTINCT T1.Col2 
 FROM Table T1 INNER JOIN Table T2
 ON T2.Col2 = T1.Col2 AND T2.Col1 = (T1.Col1 + 1)
 WHERE T1.Col3 = 'A' AND T2.Col3 = 'B'

Update: As mentioned by Peter Lang, below, this will not work if the sequence in Col1 is interrupted. This version handles that situation and is more guaranteed to produce the correct result although if you're 100% certain the sequence will not be interrupted (that is, if you generate the sequence yourself in the same transaction as the analysis) the first should be faster:

 SELECT DISTINCT T1.Col2 
 FROM Table T1 INNER JOIN Table T2
 ON T2.Col2 = T1.Col2 
 AND T2.Col1 = (SELECT MIN(Col1) FROM Table T3 WHERE T3.Col1 > T1.Col1)
 WHERE T1.Col3 = 'A' AND T2.Col3 = 'B'

Answer 5

I think the following should work, assuming your updated table layout with 3 columns. (Otherwise it's impossible, because no ordering is available):

select t1.col2
from yourtable t1, yourtable t2
where t1.col3 = 'A'
and t2.col3 = 'B'
and t1.col1 + 1 = t2.col1;

Answer 6

This query assumes that col1 is some sort of sequence or timestamp for each row. Without it, there's no way to determine if A happened before or after B.

WITH sorted AS 
(SELECT col1, col2, col3, ROWNUMBER() 
    OVER (PARTITION BY col2 ORDER BY col1) AS col4
FROM sometable
)
SELECT a.col1, a.col2, a.col3, b.col1, b.col3
FROM sorted a INNER JOIN sorted b
ON a.col2 = b.col2
WHERE a.col3 = 'A' AND b.col3 = 'B'
AND b.col4 = a.col4 + 1
;