How to avoid grouping on specific columns

Question

I have a data table about appointments, and when the related data is updated, each appointment can have multiple rows of data. I want to select the last record of each appointment to get the latest snapshot of each appointment.

In the attached code, I'm forced to group by close_pallets and close_units, which affects what I'm seeing (i.e. multiple rows returned per appointment). I want to group by a.appointment_id only so I get one row per appointment. What should I do?

SELECT
MAX(appointment_record_version_number),
appointment_id,
appointment_pallets AS close_pallets,
appointment_units AS close_units
FROM
b.dh
WHERE
last_updated_datetime BETWEEN '2023-06-01' AND '2023-06-30'
AND warehouse_id = 'xxx'
GROUP BY
appointment_id,
close_pallets,
close_units

Answer 1

You will need to use a subquery to achieve this. Actually, you need to get the maximum recorded version for each appointment ID:

SELECT
    appointment_record_version_number,
    appointment_id,
    appointment_pallets AS close_pallets,
    appointment_units AS close_units
FROM
    b.dh AS t1
WHERE
    t1.appointment_record_version_number = (
        SELECT
            MAX(appointment_record_version_number)
        FROM
            b.dh
        WHERE
            b.dh.data = t1.data
    )
    AND last_updated_datetime BETWEEN '2023-06-01' AND '2023-06-30'
    AND warehouse_id = 'xxx'

You can also use a JOIN statement to select the maximum value, which is sometimes faster:

SELECT
    t1.appointment_record_version_number,
    t1.appointment_id,
    t1.appointment_pallets AS close_pallets,
    t1.appointment_units AS close_units
FROM
    b.dh AS t1
LEFT JOIN b.dh AS t2 ON (
    t1.appointment_record_version_number = t2.appointment_record_version_number
    AND t1.appointment_id < t2.appointment_id
)
WHERE t2.appointment_record_version_number IS NULL
AND last_updated_datetime BETWEEN '2023-06-01' AND '2023-06-30'
AND warehouse_id = 'xxx';

Depending on your use case, especially if your database is large, you can use additional subqueries or indexes to further optimize the request, but it's already pretty fast.