MySQL Query Join Table Selecting Highest Date Value
- by ALHUI
Here is the query that I run
SELECT cl.cl_id, cc_rego, cc_model, cl_dateIn, cl_dateOut
FROM courtesycar cc LEFT JOIN courtesyloan cl
ON cc.cc_id = cl.cc_id
Results:
1 NXI955 Prado 2013-10-24 11:48:38 NULL
2 RJI603 Avalon 2013-10-24 11:48:42 2013-10-24 11:54:18
3 RJI603 Avalon 2013-10-24 12:01:40 NULL
The results that I wanted are to group by the cc_rego values and print the most recent cl_dateIn value. (Only Display Rows 1,3)
Ive tried to use MAX on the date and group by clause, but it combines rows, 2 & 3 together showing both the highest value of dateIn and dateOut.
Any help will be appreciated.
