3

I was hoping someone could steer me in the right direction with this one. I have a table that has student grades.

Create Table StudentGrades
( 
  StudentID references Student(StudentID) int not null,
  GradeIssued varchar(1) not null,
  WhenIssued datetime not null default(getdate())
)

I'd like to write a query that does something like: Find students who have grades (in sequence) of a D, then a C, then another C, then an A. I know the direction (because of the date the grades are issued) that the pattern should be in, but I can't think of how to write this without using a cursor.

In my experience with querying I'm filtering through via WHERE and HAVING clauses, but I'm not sure where to go when I want to find a particular pattern.

1
  • Please show some sample data and desired results. Word problems are hard. Commented Mar 27, 2015 at 18:03

2 Answers 2

5

One way of doing it would be to use LEAD.

WITH T AS
(
SELECT  StudentID,
        GradeIssued AS g1, 
        LEAD(GradeIssued, 1) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g2,
        LEAD(GradeIssued, 2) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g3,
        LEAD(GradeIssued, 3) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g4
FROM StudentGrades
)
SELECT DISTINCT StudentID
FROM   T
WHERE  g1 = 'D'
       AND g2 = 'C'
       AND g3 = 'C'
       AND g4 = 'A' 

Another way would be to use XML PATH to concatenate all grades in order and LIKE.

WITH sg1 AS
(
SELECT DISTINCT StudentID
FROM StudentGrades
)
SELECT *
FROM sg1
CROSS APPLY
(
    SELECT GradeIssued + ''
    FROM StudentGrades sg2
    WHERE sg2.StudentID = sg1.StudentID
    ORDER BY WhenIssued
    FOR XML PATH('')
) CA(grades)
WHERE grades LIKE '%DCCA%'
2
  • 1
    Well, word problems are hard unless you're Martin, nice. Commented Mar 27, 2015 at 18:59
  • No worries, missed you at Bits! Commented Mar 27, 2015 at 19:01
2

What you need to do is use the LEAD windowing function to look at one row down, two rows, three rows, etc for however long your pattern is. Example: LEAD(gradeissued,1) over (partition by studentid order by whenissued) as one_row. Put that in a CTE or subquery and then in the next query do a where criteria on your pattern (one_row = 'D' and two_row = 'C' and three_row = 'C' and four_row = 'A').

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.