MySQL Query Join Table Selecting Highest Date Value

Posted by ALHUI on Stack Overflow See other posts from Stack Overflow or by ALHUI
Published on 2013-10-24T03:49:11Z Indexed on 2013/10/24 3:53 UTC
Read the original article Hit count: 195

Filed under:
|
|
|
|

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. 1 NXI955 Prado 2013-10-24 11:48:38 NULL
  2. 2 RJI603 Avalon 2013-10-24 11:48:42 2013-10-24 11:54:18
  3. 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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about date