Get a column (id) from mysql table where created on column less than specific date

Question

I have below columns in book_history table. Every new book taken will have entry in this table with a new auto increment id, current date, student id and book id.

Id, RecordCreatdOn, StudentId, BookId

I want to get students (studentId) who have last taken any book before '2017-12-31'. Can anyone help me with the query to fetch the same ?

Thanks for the information. I think this community is to help people, not to criticize from morning till night... — Lin toka, Mar 22 '18 at 16:19

juergen d · Answer 1 · 2018-03-22T16:00:44.650

3

You need to group by the student to get every students last book with max(RecordCreatdOn)

select studentId
from book_history
group by studentId
having max(RecordCreatdOn) < '2017-12-31'

edited Mar 22 '18 at 16:00

answered Mar 22 '18 at 15:57

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Mar 22 '18 at 15:57

0

That is straight forward:

 Select Distinct StudentID from book_history where 
    RecordCreatedOn < @yourDate

answered Mar 22 '18 at 15:57

Hussein Salman

7,806
15
60
98

This won't work - if a user checked out a book before and after `@yourDate` they would be returned by this – Aaron Dietz Mar 22 '18 at 15:59
He need students who have took any book before a specific date. Didn't understand why it wouldn't work – Hussein Salman Mar 22 '18 at 16:01
This part `who have last taken any book before '2017-12-31'` implies they also haven't taken a book since then. I missed it too at first, almost posted the same answer – Aaron Dietz Mar 22 '18 at 16:30

score 0 · Answer 3 · answered Mar 22 '18 at 15:57

If you google 'mysql date comparisons', you'll get a LOT of Stackoverflow examples. Look at answers such as: mysql date comparison with date_format

That can give you at least somewhere to start.

Try this query:

Select StudentId from book_history
WHERE DATE(RecordCreatdOn) <= '2017-12-31'

Get a column (id) from mysql table where created on column less than specific date

3 Answers3