Doing a JOIN is one thing you might need.
SELECT l.id, l.time, r.time FROM
idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id
I suppose the outer join is deliberate, and you want to be getting nulls. More on that later.
WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC;
You only want the r. row that has the lowest (MIN) time that is higher than the l.time. That is the place where you need subquerying.
WHERE r.time = (SELECT MIN(time) FROM idtimes r2 where r2.id = l.id AND r2.time > l.time)
Now to the nulls. If "there is no next-higher time", then the SELECT MIN() will evaluate to null (or worse), and that itself never compares equal to anything, so your WHERE clause will never be satisfied, and the "highest time" for each ID, could never appear in the result set.
You solve it by eliminating your JOIN, and moving the scalar subquery into the SELECT list :
SELECT id, time,
(SELECT MIN(time) FROM idtimes sub
WHERE sub.id = main.id AND sub.time > main.time) as nxttime
FROM idtimes AS main
So it turns out that confluence uses a different structure to manage this data and now the discrepancies appear to be resolved.
Here is the query:
select
User_Name,
First_Name,
Last_Name,
max(Last_Login) as Last_Login
FROM
(
SELECT
u.user_name as User_Name,
u.first_name as First_Name,
u.last_name Last_Name,
ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
SELECT
u.user_name,
u.first_name as First_Name,
u.last_name as Last_Name,
date_format(l.successdate, '%Y-%m-%d') as Last_Login
FROM confluence.logininfo l
JOIN confluence.user_mapping m ON m.user_key = l.username
JOIN confluence.cwd_user u ON m.username = u.user_name
JOIN confluence.cwd_directory d ON u.directory_id = d.id and u.active = 'T'
UNION
select
u.user_name as User_Name,
u.first_name as First_Name,
u.last_name Last_Name,
ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc
Best Answer
I've set up the next example:
First you need to know the MAX disposition of every date:
Then you can obtain MAX Stamp by disposition and date.
dbfiddle here