0

(Apologies if this sounds familiar, I deleted and re-uploaded my question as I had tagged the wrong version of SQL in error)

I have a table with data on appointments, each appointment can have multiple rows of data as and when associated data is updated. I want to choose the last record for each appointment to understand the latest snapshot for each appointment.

In the attached code I am being forced to group by close_pallets and close_units, which is affecting what I see (ie it returns multiple rows per appointment). I want to only group by appointment_id so that I get a single row per appointment. How do I do this?

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
D D
  • 1
  • 1

2 Answers2

1

You can use brilliant distinct on with custom ordering instead of group by to achieve your result.

SELECT DISTINCT ON (appointment_id)
  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'
ORDER BY appointment_id,
         appointment_record_version_number desc; -- pick the biggest one
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
0

This shall give you the desired output using group by.

SELECT MAX(appointment_record_version_number),
appointment_id,
MAX(appointment_pallets) AS close_pallets,
MAX(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
MMM
  • 3,132
  • 3
  • 20
  • 32
  • Thank you! Just for my understanding (I am self taught and relatively new to this) - why do I need max on appointment_pallets and appointment_units? Reading the documentation I thought this meant I would return the largest values for these columns, not the corresponding values for the largest version number? – D D Jul 18 '23 at 11:22