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

Question

I need a SQL query from the below:

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 one is the 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 the tests(# of attempts), need to show the recent pass entries and If User failed the tests(# of attempts), need to show the recent fail entries.

Here is my query:

SELECT *, COUNT(tId), MAX(date)
FROM user_results
WHERE DATE_ADD(date, INTERVAL 1 YEAR ) >= DATE_SUB( CURDATE(), INTERVAL 1 YEAR )
GROUP BY cId, tId
HAVING COUNT(tId) =1 OR (pass = 'Y' AND COUNT(tId) >=2)

Answer 1

Please check with the following query,

1) You have to add the failed cases in HAVING clause and will get the both pass and fail results.

2) Concat the pass and date column with '@' separator and will get the value: Y@7/8/2013 10:24:47

3) Sorting with this value you will get the recent pass and recent fail.

SELECT *, COUNT(tId), MAX(date), 
SUBSTRING_INDEX(MAX(CONCAT(pass, '@', date)), '@', -1) AS max_date, 
SUBSTRING_INDEX(MAX(CONCAT(pass, '@', date)), '@', 1) AS pass_stat
FROM user_results
WHERE DATE_ADD(date, INTERVAL 1 YEAR ) >= DATE_SUB( CURDATE(), INTERVAL 1 YEAR )
GROUP BY cId, tId
HAVING COUNT(tId) =1 OR (pass = 'Y' AND COUNT(tId) >=2) OR (pass = 'N' AND COUNT(tId) >=2)
ORDER BY date DESC