How to obtain the latest date in sql

Question

Hi

I have a script that i need to output only the row with the most recent date. The script is outputting over 40000 rows. In the example shown there are 118 rows for this person with unique ID but i only want to show the row with the most recent date and similar for every person with unique ID. In this example i only want the row with DateSet 2023-09-14 00:00:00.000. Can someone help with this please

Thanks

SELECT DISTINCT 
                  hratency.tency_seq_no, QLHGM_Client.ClientName, hratency.prty_ref, QLHGM_Client.Age, hratency.tency_st_dt, hratency.tency_end_dt, hracracr.prim_clnt_yn, hratency.tenure_type, hratency.cur_tncy_bal AS [Balance Including Court Costs], QLHGM_Properties.HousingManFullName, 
                  QLHGM_Properties.HousingOffFullName, CASE WHEN SUBACCS_BAL_VIEW.balance <= 599.99 AND QLHGM_Properties.HousingManFullName = 'Elaine Hannigan' AND 
                  QLHGM_Properties.HousingOffFullName = 'William McKee' THEN 'William' WHEN SUBACCS_BAL_VIEW.balance <= 599.99 AND QLHGM_Properties.HousingManFullName = 'Elaine Hannigan' AND 
                  QLHGM_Properties.HousingOffFullName = 'Fiona Kirk' THEN 'Fiona' WHEN SUBACCS_BAL_VIEW.balance <= 599.99 AND QLHGM_Properties.HousingManFullName = 'Suzanne McLeary' AND 
                  QLHGM_Properties.HousingOffFullName = 'Karen Wilson' THEN 'Karen' WHEN SUBACCS_BAL_VIEW.balance <= 599.99 AND QLHGM_Properties.HousingManFullName = 'Suzanne McLeary' AND 
                  QLHGM_Properties.HousingOffFullName = 'Sarah McLeod' THEN 'Sarah' WHEN (SUBACCS_BAL_VIEW.balance >= 600.00 AND QLHGM_Properties.HousingManFullName = 'Elaine Hannigan') THEN 'Elaine' WHEN (SUBACCS_BAL_VIEW.balance >= 600.00 AND 
                  QLHGM_Properties.HousingManFullName = 'Suzanne McLeary') THEN 'Suzanne' WHEN (SUBACCS_BAL_VIEW.balance > 0.00 AND QLHGM_Properties.HousingManFullName = 'Louise McDonald') THEN 'Louise' ELSE ' ' END AS [Arrears Officer], 
                  CASE WHEN SUBACCS_BAL_VIEW.balance <= 199.99 THEN '£0.01-£199.99' WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 200.00 AND 399.99) THEN '£200-£399.99' WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 400.00 AND 599.99) 
                  THEN '£400-£599.99' WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 600.00 AND 799.99) THEN '£600-£799.99' WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 800.00 AND 999.99) THEN '£800-£999.99' WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 1000.00 AND 1499.99) 
                  THEN '£1000-£1499.99' WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 1500.00 AND 1999.99) THEN '£1500-£1999.99' WHEN SUBACCS_BAL_VIEW.balance > 2000.00 THEN '£2000 +' ELSE ' ' END AS [Arrears Band], hratncy2.addn_gn_cd2, 
                  CASE WHEN SUBACCS_BAL_VIEW.balance <= 199.99 THEN 10 WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 200.00 AND 399.99) THEN 20 WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 400.00 AND 599.99) THEN 30 WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 600.00 AND 
                  799.99) THEN 40 WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 800.00 AND 999.99) THEN 50 WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 1000.00 AND 1499.99) THEN 60 WHEN (SUBACCS_BAL_VIEW.balance BETWEEN 1500.00 AND 1999.99) 
                  THEN 70 WHEN SUBACCS_BAL_VIEW.balance > 2000.00 THEN 80 ELSE 999 END AS Arrears_Band_Sort, SUBACCS_BAL_VIEW.subacc_id AS [Sub Account], SUBACCS_BAL_VIEW.balance AS [Rent Arrears Balance], QLHRA_Actions_Activities.ActionCode, QLHRA_Actions_Activities.DateSet, 
                  QLHGM_Client.TelephoneNumber1, QLHGM_Client.TelephoneNumber2, QLHGM_Client.TelephoneNumber3, QLHGM_Client.EmailAddress, hratency.paymt_last, hratency.last_paymt
FROM        hratency INNER JOIN
                  hracracr ON hratency.comp_id = hracracr.comp_id AND hratency.rent_acc_no = hracracr.rent_acc_no INNER JOIN
                  QLHGM_Client ON hracracr.comp_id = QLHGM_Client.CompanyCode AND hracracr.clnt_no = QLHGM_Client.ClientNumber INNER JOIN
                  QLHGM_Properties ON hratency.prty_ref = QLHGM_Properties.PropertyCode AND hratency.comp_id = QLHGM_Properties.CompanyCode INNER JOIN
                  hratncy2 ON hratency.comp_id = hratncy2.comp_id AND hratency.tency_seq_no = hratncy2.tency_seq_no INNER JOIN
                  SUBACCS_BAL_VIEW ON hratency.comp_id = SUBACCS_BAL_VIEW.comp_id AND hratency.tency_seq_no = SUBACCS_BAL_VIEW.tency_seq_no AND hracracr.rent_acc_no = SUBACCS_BAL_VIEW.rent_acc_no LEFT OUTER JOIN
                  QLHRA_Actions_Activities ON hratency.comp_id = QLHRA_Actions_Activities.CompanyIdentifier AND hratency.tency_seq_no = QLHRA_Actions_Activities.TenancySequenceNumber LEFT OUTER JOIN
                  hgmcntct ON hratency.comp_id = hgmcntct.comp_id AND hratency.tency_seq_no = hgmcntct.tency_seq_no
WHERE     (hracracr.type = 'J' OR
                  hracracr.type = 'P') AND (hracracr.prim_clnt_yn = 1) AND (hratency.tency_seq_no >= 200000) AND (hratency.tenure_type <> 'OWN') AND (hratency.tenure_type <> 'COM') AND (hratency.tenure_type <> 'FORMO') AND (hratency.tency_end_dt IS NULL) AND (hratency.cur_tncy_bal > 0) AND 
                  (SUBACCS_BAL_VIEW.subacc_id = 'rent') AND (SUBACCS_BAL_VIEW.balance > 0) 
ORDER BY hratency.tency_seq_no, hratency.prty_ref, QLHRA_Actions_Activities.DateSet

Answer 1

How to obtain the latest date in sql

Use a MAX aggregation, e.g. with a CTE and then JOIN over PrimaryKey and max date, like

;WITH cte AS
     (SELECT QAA.PrimaryKey, MAX(DateSet) AS MaxDateSet
      FROM QLHRA_Actions_Activities AS QAA
      GROUP BY QAA.PrimaryKey)
SELECT * 
FROM cte
     INNER JOIN
     <TheRestOfYourQuery>
          ON cte.PrimaryKey = YourQuery.PrimaryKey
             AND cte.MaxDateSet = YourQuery.DateSet

Answer 2

Try one of methods:

select *
from 
(
    select *, row_number() over (partition by ClientNumber order by DateSet desc) rn
    from 
    (
        --- your query without ORDER BY, with new column: QLHGM_Client.ClientNumber --- 
    ) t
) t
where rn = 1
order by tency_seq_no, prty_ref, DateSet

You can specify the required columns instead of '*'. If ClientNumber is not the “person unique ID”, then use the correct column.