How to get Student Details SQL Query

Question

• i have table student with ID,SID,CHECKID,DATE
• here ID : tableID(auto increment), SID : studentID ,CHECKID : 0/1(i.e. IN/OUT), DATE.
• when student entered into class room he will save daily attendance by card.

• i have data is

  ID SID CHECKID DATE

  --- --- -------- -----

  0 101 0 04-10-2013 10:00:00

  1 101 1 04-10-2013 11:30:00

  2 101 0 04-10-2013 14:15:00

• i tried query like :

  select DISTINCT SID,MAX(DATE),CHECKID from student where SID='101' group by SID

• then i got results as two records.

• i need record is max date(i.e. recent record of particular student ) like : 3rd record only.

• any help.thanks in advance.

Answer 1

The fastest approach is probably to use an analytic function:

select *
  from (select s.*,
               row_number() over(partition by sid order by date desc) as rn
          from student s)
 where rn = 1

Another way is a correlated subquery:

select *
  from student s
 where date = (select max(x.date) from student x where x.sid = s.sid)

Another way is to join into an inline view:

select s.*
  from student s
  join (select sid, max(date) as sid_last_date from student group by sid) v
    on s.sid = v.sid
   and s.date = v.sid_last_date