How to get student information SQL query

  • I have a table student with ID, SID, CHECKID, DATE.
  • here ID: tableID (auto-increment), SID: studentID, CHECKID: 0/1 (i.e. IN / OUT), DATE.
  • When the student entered the classroom, he will save the daily attendance card.
  • I have data


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

    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 a query like:

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

  • then I got the results as two entries.

  • I need a record - the maximum date (for example, a recent record of a specific student), for example: 3rd record.

  • any help. Thanks in advance.


source to share

1 answer

The fastest approach is probably using 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( from student x where x.sid = s.sid)


Another way is to bundle 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 = v.sid_last_date




All Articles