My solution avoids joining the table to itself by using a couple of window functions in a CTE named "flagged_data." It adds a "StudentID_Problem" column (1 for a problem, 0 for no problem). Because COUNT(DISTINCT) does not work in a window function, I compared MIN(StudentID) to MAX(StudentID) to see if they are a match. If they are not a match, there is a problem because there are at least two StudentID values for the given CaseID.

Because this flag needs to be a filter, and because window functions are not allowed in the WHERE clause, there has to be an intermediary step in a CTE. AFter the CTE, though, it is a simple SELECT statement ordered by a LastEntryDate (also calculated with a window function in the CTE), CaseID, and CreatedDate.

with

mySampleTable (CaseID, StudentID, StudentSocial, StudentName, CreatedDate) as

(select 1001, 'FTS-2134', '111-22-3333', 'Roger Federer', '5/12/2013' union all

select 1001, 'FTS-2314', '111-22-3333', 'Roger Federer', '5/08/2013'union all

select 1002, 'PTS-5769', '222-33-4444', 'Maria Sharapova', '5/14/2013'union all

select 1002, 'PTS-5769', '222-33-4444', 'Maria Sharapova', '5/2/2013'union all

select 1003, 'FTS-8764', '333-44-5555', 'Serena Williams', '5/17/2013'union all

select 1003, 'FTS-8764', '333-44-5555', 'Serena Williams', '5/06/2013'union all

select 1003, 'FTS-7864', '333-44-5555', 'Serena Williams', '4/30/2013'union all

select 1004, 'FTS-9132', '444-55-6666', 'Novak Djokovic', '5/10/2013'union all

select 1004, 'FTS-9312', '444-55-6666', 'Novak Djokovic', '4/27/2013'union all

select 1005, 'PTS-4722', '555-66-7777', 'Novak Djokovic', '5/19/2013'union all

select 1005, 'PTS-4722', '555-66-7777', 'Novak Djokovic', '5/12/2013'),

flagged_data as

(SELECT

CaseID,

StudentID,

StudentSocial,

StudentName,

CreatedDate,

StudentID_Problem = Case when MIN(StudentID) over (PARTITION by CaseID) <> MAX(StudentID) over (partition by CaseID) then 1 else 0 end,

LastEntryDate = MAX(CreatedDate) over (partition by CaseID)

FROM

mySampleTable)

select

CaseID,

StudentID,

StudentSocial,

StudentName,

CreatedDate

from

flagged_data

where

StudentID_Problem = 1

order by

LastEntryDate desc, CaseID, CreatedDate desc