MYSQL Select to return most recent date and next date in same result row

I have a "Roster" table which has two fields - the userid and the date of their shift.  I would like a query that returns a single row for each user, with their most recent past shift and their next future shift.  I can get one or the other (ie either past or future) but not both.  I'm sure there's some simple answer.

eg if today's date is 2007-03-30
Userid         Date
0001           2007-03-02
0002           2007-03-04
0003           2007-03-05
0002           2007-03-06
0001           2007-03-07
0003           2007-04-10
0002           2007-04-11
0002           2007-04-12
0003           2007-04-13
0004           2007-04-14

would return
userid      last-shift         next-shift
0001        2007-03-07    Null
0002        2007-03-06    2007-04-11
0003        2007-03-05    2007-04-10
0004        Null                 2007-04-14

Many thanks in advance
PS I looked at Q22445165 but there don't seem to be the right number of parentheses (brackets) so I couldn't work out how to apply that solution to my problem.
David LinkerAsked:
Who is Participating?
momi_sabagConnect With a Mentor Commented:
i'm sorry
the query i entered may not be correct,
try this one :

select   t1.userid,  as last_shift,  as next_shift
(select userid, max(date) from mytable where date < now() group by userid)  t1,
(select userid, min(date)  from mytable where date > now() group by userid) t2
where t1.userid = t2.userid

that would work
select   t1.userid, max( as last_shift, min( as next_shift
from  mytable t1, mytable t2
where t1.userid = t2.userid
and < now
and > now

maybe i'm wrong with the now function - you need the function that returns todays date

that should be

select   t1.userid, max( as last_shift, min( as next_shift
from  mytable t1, mytable t2
where t1.userid = t2.userid
and < now()
and > now()
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

I create table roster,inserted one userid with 2 dates before and after now and executed this query
SELECT l.userid 'User ID', Max( ) AS 'Last Shift', Min( ) AS 'Next Shift'
FROM roster l
INNER JOIN roster n ON l.userid = n.userid
AND > now( )
WHERE <= now( )
GROUP BY l.userid

and this is the result
Showing rows 0 - 0 (1 total, Query took 0.0530 sec)
User ID  Last Shift  Next Shift  
1 2007-03-29 2007-04-01
David LinkerAuthor Commented:
I'm giving the points to momi_sabaq as he provided an answer first - and his query runs faster (0.0115 vs 0.0374 on my real data) than mukhtar2t's solution.  However, I had to make a change (moved the "AS" statements into each sub query) to get the query to work so I only gave a B rating:
SELECT t1.userid, last_shift, next_shift

SELECT userid, max( date ) AS last_shift
FROM roster
WHERE date <= now( )
GROUP BY userid)t1, (

SELECT userid, min( date ) AS next_shift
FROM roster
WHERE date > now( )
GROUP BY userid)t2,
WHERE t1.userid = t2.userid
David LinkerAuthor Commented:
Thanks to both of you for the quick respons and the help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.