Those 2 queries give very different results and your simplified version gives the wrong result. This is small sample of all the columns of the data: ID DATE_STATUS_ADDED LINKS_REG_STATUS REG_STATUS PATIENT_TYPE DATE_STATUS_ENDED 239 23527 Application Form FP1 submitted Registered Regular 23527 239 23527 Notification of registration Registered Regular 36549 239 36549 Death Deceased Regular 0 246 32212 Application Form FP1 submitted Registered Regular 32212 246 32212 Notification of registration Registered Regular 39217 246 39217 Internal transfer - address change Left Regular 0 7340 31937 Application Form FP1 submitted Registered Regular 31937 7340 31937 Notification of registration Registered Regular 42451 7340 42451 Other reason Left Regular 42451 7340 42451 Record Requested by FHSA Left Regular 42453 7340 42453 Patient has presented Registered Regular 42453 7340 42453 Application Form FP1 submitted Registered Regular 42453 7340 42453 Record Received Registered Regular 42458 7340 42458 Notification of registration Registered Regular 42467 7340 42467 Patient has presented Registered Regular 42467 7340 42467 Record Received Registered Regular 42467 7340 42467 Application Form FP1 submitted Registered Regular 42482 7340 42467 Other reason Left Regular 42467 7340 42482 Notification of registration Registered Regular 44200 7340 44200 Death Deceased Regular 44202 7340 44202 Record Requested by FHSA Left Regular 44228 7340 44228 Records sent back to FHSA Left Regular 0 19375 35403 Application Form FP1 submitted Registered Regular 35403 19375 35403 Notification of registration Registered Regular 35403 19375 35403 Death Deceased Regular 0 One complication is that there are multiple entries on the same date, but with completely different meanings. I have solved this now by copying the sorted data to a new table: insert into REG_HISTORY_ALL2 select ID, DATE_STATUS_ADDED, LINKS_REG_STATUS, REG_STATUS, PATIENT_TYPE, DATE_STATUS_ENDED from REG_HISTORY_ALL order by id asc, DATE_STATUS_ADDED asc, REG_STATUS desc This simplifies my single date query: select xsd2xld(23/04/2021) as Date, count(*) as Cnt from (select id, max(rowid) as last_date from reg_history_all2 where date_status_added < xsd2xld(23/04/2021) group by id having reg_status = 'Registered') As now the row with 'Deceased' will be selected after the row with 'Registered if they have the same date_status_added. RBS