CODEWITHSUNDEEP
sql
NIBULA
NIBULA

Reputation: 21

How to show which students are still in school using sql

This table shows the records of students entering and leaving the school. IN represents student entering school and OUT represents student leaving school. I wondering how to show which students are still in school.

I'm trying so much but still cannot figure it out, does anyone can help me, Thank you so much.

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL auto_increment,
  `time` varchar(128) default NULL,
  `status` varchar(128) default NULL,
  `stu_id` varchar(128) default NULL,
  PRIMARY KEY  (`id`)
) 

INSERT INTO `student` (`id`, `time`, `status`, `stu_id`) VALUES 
    (1,'11AM','IN','1'),
    (2,'11AM','IN','2'),
    (3,'12AM','OUT','1'),
    (4,'12AM','IN','3'),
    (5,'1PM','OUT','3'),
    (6,'2PM','IN','3'),
    (11,'2PM','IN','4');

I expect the answer is 2, 3, 4

Upvotes: 1

Views: 104

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The number of students in the school is the sum of the ins minus the sum of the outs:

select sum(case when status = 'in' then 1
                when status = 'out' then -1
                else 0
           end)
from student;

Basically to see the students who are in the school, you want the students whose last status is in. One way uses a correlated subquery:

select s.stu_id
from student s
where s.time = (select max(s2.time)
                from student s2
                where s2.stu_id = s.stu_id
               ) and
      s.status = 'in';

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

here's the query considering the auto increment id

select t2.* from  
student t2 
left join (select ROW_NUMBER() OVER(PARTITION by stu_id ORDER BY id desc) as row_num, id from student) t1 on t1.id = t2.id
where t1.row_num = 1 and [status] = 'IN'

Upvotes: 0

developer
developer

Reputation: 45

If status is either only IN or OUT can't you do SELECT * from student WHERE status="IN"

Upvotes: 0

Related Questions