Question 210462 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search