How to validate the highest value received in mysql?

Question

How to validate the highest value received in mysql?

Navigation

#1 by (0 votes)

0

I have a query that returns me the last accesses of the users in the system. I would like to know how I could handle it to return only dates that were longer than 15 days.

The query is this:

SELECT u.id, concat(u.firstname,' ',u.lastname) as nome,from_unixtime(MAX(l.time)),
DATEDIFF(now(),from_unixtime(MAX(l.time))) as Dias
FROM mdl_role_assignments rs
    INNER JOIN mdl_user u ON u.id=rs.userid
    INNER JOIN mdl_context e ON rs.contextid=e.id
    INNER JOIN mdl_log l ON l.userid=u.id
WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=2174 AND l.course=e.instanceid
GROUP BY u.id, u.firstname,u.lastname
order by Dias desc

The main table for this operation is the mdl_log that stores those records next to the iduser and course (which I used to filter the course of id = 2174 in e.instanceid )

I have tried to pass the MAX () function in the where, I tried to pass that same SELECT into WHERE by comparing the return with any date ... without success.

mysql sql select moodle

asked by anonymous 23.03.2018 / 11:43

1 answer

Show template according to user status Repeat a CSS animation every time I click the button (with Javascript)

score 0 · Answer 1

Try to make the comparison in WHERE as indicated by @Ricardo Pontual

SELECT u.id, concat(u.firstname,' ',u.lastname) as nome, from_unixtime(MAX(l.time)),
DATEDIFF(now(),from_unixtime(MAX(l.time))) as Dias
FROM mdl_role_assignments rs
    INNER JOIN mdl_user u ON u.id=rs.userid
    INNER JOIN mdl_context e ON rs.contextid=e.id
    INNER JOIN mdl_log l ON l.userid=u.id
WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=2174 AND l.course=e.instanceid AND from_unixtime(l.time) > (CURDATE() - INTERVAL 15 DAY)
GROUP BY u.id, u.firstname,u.lastname
order by Dias desc

This will filter all records for the last 15 days, and then, if any, the last of these dates will be displayed. If it is to return ALL dates longer than 15 days (more than one record) remove GROUP BY and MAX.