CODEWITHSUNDEEP
postgresqlpostgresql-8.0
D D
D D

Reputation: 1

How to limit group by statement to 1 column

(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

Upvotes: 0

Views: 38

Answers (2)

Midhun Mundayadan
Midhun Mundayadan

Reputation: 3182

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

Upvotes: 0

Stefanov.sm
Stefanov.sm

Reputation: 13049

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

Upvotes: 1

Related Questions