How to Identify Specific (Non-numeric) Sequence in SQL Server Records?

Question

Say I have a list of students and the time their attendance was taken in specific class types. How would I SELECT in SQL Server those students who attended a Math class directly followed by Science? Sample data as follows (adding spaces between students and sequencing by AttendanceDateTime for clarity):

Attendance for...

[Student]             [ClassAttended]     [AttendanceDateTime]
---------             ---------------     --------------------  
John                  Math                2018-07-01 08:04:58.277
John                  Science             2018-07-01 11:00:16.201
John                  Composition         2018-07-01 14:03:10.112

Edward                Math                2018-07-01 08:05:58.277
Edward                Composition         2018-07-01 11:01:16.201
Edward                Science             2018-07-01 14:02:10.112

Robert                Math                2018-07-01 08:03:58.277
Robert                Science             2018-07-01 11:02:16.201
Robert                Composition         2018-07-01 14:01:10.112

Allen                 Composition         2018-07-01 08:02:58.277
Allen                 Math                2018-07-01 11:03:16.201
Allen                 Science             2018-07-01 14:00:10.112

Results I am looking for:


[Student]           
---------           
John                
Robert              
Allen

I have looked at both How to find consecutive data in SQL and How to find consecutive rows based on the value of a column? for a while now and I think the idea is there but I'm not getting it. Any pointer in the right direction is much appreciated.

score 2 · Accepted Answer · answered Jul 31 '18 at 16:56

2

One way to solve this is with the Lead() function in a subquery:

SELECT DISTINCT student
FROM
    (
        SELECT table.*, Lead(ClassAttended) OVER (PARTITION BY Student ORDER BY AttendanceDateTime) as next_class_attended 
        FROM table
     )sub
WHERE classAttended = 'Math' AND next_class_attended = 'Science'

answered Jul 31 '18 at 16:56

JNevill

46,980
4
38
63

So I've been doing validation and I'm getting different results than expected, but I think it's because there is more complexity in the real data than in this example. I'm going to mark this as the answer though because the Lead () function should accomplish the task. – RiSt Aug 01 '18 at 18:59

How to Identify Specific (Non-numeric) Sequence in SQL Server Records?

1 Answers1