Sql to Select record by latest date and decided by that

Question

I didn't know what to write for title I have table like this:

RegistrationNumber- Class- Score- Session Date - Session Time
2000905         | Class1  | 80    |2014-07-13|  10:00-13:00 
2000019         | Class1  | 70    |2014-07-19|  15:00-18:00
2000007         | Class1  | 70    |2014-07-18|  10:00-13:00 
2000007         | Class1  | 70    |2014-07-14|  15:00-18:00 
2000007         | Class1  | 70    |2014-07-11|  10:00-13:00 
2000907         | Class2  | Null  |2014-07-12|  15:00-18:00
2000006         | Class2  | 34    |2014-07-13|  10:00-13:00 
2005612         | Class2  | 55    |2014-07-14|  15:00-18:00 
2000919         | Class2  | 65    |2014-07-15|  11:00-12:00 
2001034         | Class2  | 29    |2014-07-14|  11:00-12:00 
2000000         | Class2  | 45    |2014-07-14|  11:00-12:00 
2000000         | Class2  | 45    |2014-07-14|  15:00-18:00 
2000000         | Class2  | 45    |2014-07-15|  10:00-13:00
2001029         | Class2  | Null  |2014-07-17|  10:00-13:00 

I want to write a SQL which brings datas as shown below The idea is sorting by RegistrationNumber, Class,SessionDate,SessionTime and then whoever has score and one session then but Y(Yes ) for Attendance . If the same person has more than 1 session for the same class then it should put Y only for the lastest session and the previous ones should be No

RegistrationNumber- Class- Score- Session Date - Session Time -Attendance   

2000000         | Class2  | 45    |2014-07-14|  11:00-12:00 |   N           
2000000         | Class2  | 45    |2014-07-14|  15:00-18:00 |   N           
2000000         | Class2  | 45    |2014-07-15|  10:00-13:00 |   Y           
2000007         | Class1  | 70    |2014-07-11|  10:00-13:00 |   N           
2000007         | Class1  | 70    |2014-07-14|  15:00-18:00 |   N           
2000007         | Class1  | 70    |2014-07-18|  10:00-13:00 |   Y           
2000006         | Class2  | 34    |2014-07-13|  10:00-13:00 |   Y           
2000019         | Class1  | 70    |2014-07-19|  15:00-18:00 |   Y           
2000905         | Class1  | 80    |2014-07-13|  10:00-13:00 |   Y           
2000907         | Class2  | Null  |2014-07-12|  15:00-18:00 |   N          
2000919         | Class2  | 65    |2014-07-15|  11:00-12:00 |   Y           
2001029         | Class2  | Null  |2014-07-17|  10:00-13:00 |   N          
2001034         | Class2  | 29    |2014-07-14|  11:00-12:00 |   Y           
2005612         | Class2  | 55    |2014-07-14|  15:00-18:00 |   Y       

Answer 1

Here is an idea. Get the information you want using window functions. Then just use these in a case statement. I think the logic is:

select RegistrationNumber, Class, Score, SessionDate, SessionTime,
       (case when score is not null and cnt = 1 then 'Y'
             when cnt > 1 and seqnum = cnt then 'Y'
             else 'N'
        end) as Attendance
from (select t.*,
             row_number() over (partition by RegistrationNumber, Class
                                order by SessionDate, SessionTime) as seqnum,
             count(*) over (partition by RegistrationNumber, Class) as cnt
      from table t
     ) t
order by RegistrationNumber, Class, SessionDate, SessionTime;

Answer 2

Just to show an alternative to Gordon's answer: You want the last record per registrationnumber and class and make it a 'Y' (provided it has a score). So this is the first in reverse order. Use row_number with the reverse order hence to determine this first or rather last record :-)

select
  registrationnumber, class, score, session_date, session_time,
  case when score > 0 and row_number() over (
                           partition by registrationnumber, class 
                           order by session_date desc, session_time desc) = 1 then
    'Y'
  else
    'N'
  end as attendance 
from mytable
order by registrationnumber, class, session_date, session_time;