sql - How can I get the maximum date of both passed and failed results using MySQL? -

i new mysql. need sql query below expected results. please check following,

table: user_results

id  cid uid tid pass    date 1   23  34  2   y   7/8/2013 10:24:47 2   23  34  2   n   11/27/2014 10:36:32 3   23  34  3   y   12/9/2013 10:24:47 4   23  34  3   n   11/27/2014 10:39:10 5   23  34  4   y   10/26/2013 10:24:47 6   23  34  4   n   11/27/2014 10:38:08 7   59  93  2   y   11/24/2013 9:34:23 8   69  82  2   y   11/28/2014 9:04:22 9   69  82  2   y   11/28/2014 8:59:52 10  69  82  4   y   11/28/2014 8:59:52 11  69  82  4   y   11/28/2014 9:10:40 12  69  82  4   n   11/28/2014 9:12:01 13  72  72  2   n   12/1/2014 6:46:02 14  73  69  2   n   12/1/2014 6:49:29 15  73  69  3   n   12/1/2014 6:51:31 16  73  69  3   n   12/1/2014 7:11:25 

below 1 expected results,

id  cid uid tid pass    date 1   23  34  2   y   7/8/2013 10:24:47 3   23  34  3   y   12/9/2013 10:24:47 5   23  34  4   y   10/26/2013 10:24:47 7   59  93  2   y   11/24/2013 9:34:23 9   69  82  2   y   11/28/2014 8:59:52 11  69  82  4   y   11/28/2014 9:10:40 13  72  72  2   n   12/1/2014 6:46:02 14  73  69  2   n   12/1/2014 6:49:29 16  73  69  3   n   12/1/2014 7:11:25 

note: if user passed tests(# of attempts), need show recent pass entries , if user failed tests(# of attempts), need show recent fail entries.

here query:

select *, count(tid), max(date) user_results date_add(date, interval 1 year ) >= date_sub( curdate(), interval 1 year ) group cid, tid having count(tid) =1 or (pass = 'y' , count(tid) >=2) 

please help! advance!

please check following query,

1) have add failed cases in having clause , both pass , fail results.

2) concat pass , date column '@' separator , value: y@7/8/2013 10:24:47

3) sorting value recent pass , recent fail.

select *, count(tid), max(date),  substring_index(max(concat(pass, '@', date)), '@', -1) max_date,  substring_index(max(concat(pass, '@', date)), '@', 1) pass_stat user_results date_add(date, interval 1 year ) >= date_sub( curdate(), interval 1 year ) group cid, tid having count(tid) =1 or (pass = 'y' , count(tid) >=2) or (pass = 'n' , count(tid) >=2) order date desc