SQL display all results that match a client id but only show most recent version of each instance

Question

i have the following sql code that displays all records that match the user SPI-GVT97. It displays 3 results as you can see. I'd like it to show the most recent version of an instance if more than one exists. So the most recent version of summers place is 2014-03-08 and as they is only one instance of 26 friars view, that should still show. So ideally, only 2 results would show instead of the current 3. I have tried max(checkin_date) but it just does the most recent version of everything for that client code.

SELECT checkin_id, 
       checkin_client_id, 
       checkin_inventory_id, 
       checkin_property_id, 
       checkin_date, 
       property_address_line_1, 
       client_first_name, 
       client_last_name, 
       client_organisation_name, 
       client_unique_id 
  FROM check_in 
       INNER JOIN properties 
           ON checkin_property_id = property_id 
       INNER JOIN clients 
           ON checkin_client_id = client_id 
 WHERE client_unique_id LIKE ?

Answer 1

Append this condition at the end of the query

........
........
........
WHERE client_unique_id LIKE ?

  AND NOT EXISTS (
    SELECT 1 FROM check_in CC
    WHERE check_in.checkin_client_id = CC.checkin_client_id
      AND CC.checkin_date < check_in.checkin_date
)

---

EDIT

---

It's hard to say looking only at the query and without knowing tables structures.
A general approach using NOT EXIST works fine, look at this demo: http://sqlfiddle.com/#!2/9f99d/1

create table clients(
  client_id int primary key
);

insert into clients values(1),(2),(3);

create table check_in(
  check_in_id int primary key auto_increment,
  checkin_client_id int,
  checkin_date date
);

insert into check_in( checkin_client_id, checkin_date )
values
(1,'2014-02-01'),(1,'2014-02-03'),
(2,'2010-02-01'),(2,'2014-02-01'),(2,'2014-02-15'),
(3,'2010-12-01'),(2,'2014-01-21'),(2,'2014-02-10');

select *
from clients c
join check_in ci
on c.client_id = ci.checkin_client_id
where NOT EXISTS (
  select 1 
  from check_in cc1
  where ci.checkin_client_id = cc1.checkin_client_id
    and ci.checkin_date < cc1.checkin_date
)

| CLIENT_ID | CHECK_IN_ID | CHECKIN_CLIENT_ID |                    CHECKIN_DATE |
|-----------|-------------|-------------------|---------------------------------|
|         1 |           2 |                 1 | February, 03 2014 00:00:00+0000 |
|         2 |           5 |                 2 | February, 15 2014 00:00:00+0000 |
|         3 |           8 |                 3 | February, 10 2014 00:00:00+0000 |