SQL: Select Distinct and Max shows the lowest value instead of the highest

Question

In my table hms_bbr_group since there are multiple copies of the column group_id I am trying to have a query selecting the highest value id of each group_id, hence the DISTINCT

Table:

1

For instance, I want my query to show only id 61, group_id 36, in group_name Infection Control disregarding the other ids since I only want the highest value

My SQL select:

SELECT DISTINCT ON (hbg.group_id)
    MAX(hbg.id) AS id,
    hbg.group_id,
    hbg.group_name,
    hbg.group_description,
    hbg.group_type_id,
    hbgt.group_type_name,
    hbg.category_id,
    hbg.effective_start_datetime,
    hbg.created_by,
    hbg.created_datetime,
    hbg.archived_by,
    hbg.archived_datetime
FROM 
    hms_bbr_group hbg
LEFT JOIN 
    hms_bbr_group_type hbgt ON (hbg.group_type_id = hbgt.group_type_id)
GROUP BY 
    hbg.group_id, hbg.group_name, hbg.group_description, 
    hbg.group_type_id, hbgt.group_type_name, hbg.category_id, 
    hbg.effective_start_datetime, hbg.created_by, 
    hbg.created_datetime, hbg.archived_by, hbg.archived_datetime 
ORDER BY 
    hbg.group_id ASC;

The important lines here are

DISTINCT ON (hbg.group_id)
MAX(hbg.id) AS id

My issue is the output shows the lowest id value instead:

SQL query:

2

as you can see from the first screenshot, instead of getting id 61 on group_id 36 (highest) I am getting id 36 (lowest)

to test it out I tried replacing MAX with MIN but the output is still the same.

How come my SQL does not show the MAX id value?

Answer 1

Your use of DISTINCT ON is incorrect, and you don't need to be using GROUP BY here. Just list in parentheses in the select clause the column used for considering each group. Then repeat that column first in the ORDER BY clause, followed by the column to be used for choosing the first record in each group.

SELECT DISTINCT ON (hbg.group_id)
    hbg.group_id,
    hbg.id AS id,
    hbg.group_name,
    hbg.group_description,
    hbg.group_type_id,
    hbgt.group_type_name,
    hbg.category_id,
    hbg.effective_start_datetime,
    hbg.created_by,
    hbg.created_datetime,
    hbg.archived_by,
    hbg.archived_datetime
FROM hms_bbr_group hbg
LEFT JOIN hms_bbr_group_type hbgt
    ON hbg.group_type_id = hbgt.group_type_id
ORDER BY
    hbg.group_id,
    hbg.id DESC;