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
SID CHECK IDENTIFIER
--- --- -------- -----
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.
+3
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(x.date) 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 s.date = v.sid_last_date
+4
source to share