MySQL subquerie not working out

Question

I am new to MySQL, so be nice 🙂 The code is about students, assignments, due dates, etc I have to write a query plus subquery, that shows the name, latest due date and how many days it is late.

So far so good...

create table grades
(
lname varchar(20) not null,
fname varchar (20) not null,
due_date date,
submit_date date,
unit_num int not null,
assgn_type char(1) not null,
assgn_num int not null,
grade int not null
);

select concat(fname,' ', lname) as name, due_date, (submit_date - due_date) as days_latefrom grades
where due_date in (select max(due_date)
from grades)
order by days_late desc;

..it is working, but I have to get only 2 positive values. With my code above I get 2 positive and 2 negative numbers (days_late). I have tried: where submit_date - due_date >= 1, but I am not quite sure if and where I could use it.

Answer 1

You are in the right track. Add "and submit_date > due_date" to your where clause.