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.

enter image description here

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 ?

Not sure if i can post the actual database table code as it is sensitive information. I have posted what works in order to get the records from the database — Andy Holmes, Mar 23 '14 at 15:17
You don't qualify your columns, and you don't provide your table structure, so not sure what you want from us except I suppose something generic. — Strawberry, Mar 23 '14 at 15:28
You might find questions tagged [tag:greatest-n-per-group] helpful, for example http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group . — DCoder, Mar 23 '14 at 15:29
I literally just need it to only show the most recent version of an instance when there is more than one. I have included the code that works, which is selecting the right columns. Surely the actual table isnt needed as you've got all the stuff im calling up there? — Andy Holmes, Mar 23 '14 at 15:30

krokodilko · Accepted Answer · 2014-03-23T16:07:48.760

2

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 |

edited Mar 23 '14 at 16:07

answered Mar 23 '14 at 15:33

krokodilko

35,300
7
55
79

I'm just getting an error when i add this in. Doesn't run the query at all now – Andy Holmes Mar 23 '14 at 15:38
it's showing me the older one for 1 summers place and not the newer – Andy Holmes Mar 23 '14 at 15:42
but it does only show the 2 like i was after, just the wrong dated version – Andy Holmes Mar 23 '14 at 15:45
@AndyHolmes: change the `<` to a `>` . – DCoder Mar 23 '14 at 15:52
@DCoder That doesn't work either now i just get the latest 1 Summers Place ONLY and not the 26 Friars View – Andy Holmes Mar 23 '14 at 15:55
I'll admit that I don't see how that change would produce this difference. Which column are you taking "1 Summers Place" and "26 Friars View" from? This answer assumes that's `checkin_client_id` or something in the `clients` table. – DCoder Mar 23 '14 at 16:05
I've appended a working demo with general approach. I don't know your tables' structures and don't see the data, so it's hard to diagnose why it didn't work. – krokodilko Mar 23 '14 at 16:11

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

1 Answers1