SQL SELECT MAX is not working as intended-CodePudding

SELECT hbgu.jid,
       hbgu.id AS id,
       hbgu.group_id,
       MAX (hbgu.parent_id) AS parent_id,
       hbgu.jid,
       d.name
FROM hms_bbr_group_user hbgu
LEFT JOIN hms_doctor d
    ON hbgu.jid = d.jid
WHERE hbgu.group_id = 113
GROUP BY hbgu.id, d.name

I have a SELECT statement above that is trying to only pick the maximum value of the column parent_id. In my case, I only want parent_id = 114 because that is the highest value. My problem is that the values that are not the highest are showing up, what seems to be the issue?

UPDATE:

Create Table:

CREATE TABLE public.hms_bbr_group_user
(
    id integer NOT NULL,
    group_id integer,
    parent_id integer,
    jid character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT hms_bbr_group_user_pkey PRIMARY KEY (id)
)

CREATE TABLE public.hms_doctor
(
    jid character varying(50) COLLATE pg_catalog."default" NOT NULL,
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    mobile character varying(50) COLLATE pg_catalog."default",
    operatorid integer,
    designationid integer NOT NULL,
    departmentid integer NOT NULL,
    pager character varying(50) COLLATE pg_catalog."default",
    mcr character varying(50) COLLATE pg_catalog."default",
    created_by character varying(50) COLLATE pg_catalog."default",
    created_date timestamp without time zone,
    updated_by character varying(50) COLLATE pg_catalog."default",
    updated_date timestamp without time zone,
    escalation_status integer NOT NULL DEFAULT 1,
    transport_type integer NOT NULL DEFAULT 0,
    extension character varying(50) COLLATE pg_catalog."default",
    hospitalid integer NOT NULL,
    type character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'doctor'::character varying,
    title character varying(35) COLLATE pg_catalog."default",
    subdeptid integer,
    sectionid integer,
    groupid integer,
    location character varying COLLATE pg_catalog."default",
    privacy_status integer NOT NULL DEFAULT 0,
    remote_status integer NOT NULL DEFAULT 1,
    display_priority integer NOT NULL DEFAULT 1000,
    fax character varying(50) COLLATE pg_catalog."default",
    divisionid integer,
    subsectionid integer,
    unitid integer,
    physical_location_id integer,
    areas_of_interest text COLLATE pg_catalog."default",
    CONSTRAINT hms_doctor_pkey PRIMARY KEY (jid)
)

CodePudding user response：

Answer to address the new requirement:

If the above is correct, the next step is to simply add a JOIN with the doctor table.

Updated fiddle with doctor detail

Initial answer explaining the OP's original query result:

This is a simple functional dependence issue.

Since id is the primary key of the table, when we GROUP BY that column, there can be only one row per group (from that table) and only one parent_id per group.

The query:

SELECT hbgu.jid
     , hbgu.id AS id
     , hbgu.group_id
     , MAX (hbgu.parent_id) AS parent_id
     , hbgu.jid
  FROM hms_bbr_group_user hbgu
 WHERE hbgu.group_id = 113
 GROUP BY hbgu.id
;

Note: MAX was not necessary, since there's guaranteed to be only one parent_id value per group, based on the primary key being in the GROUP BY terms.

SELECT hbgu.jid
     , hbgu.id AS id
     , hbgu.group_id
     , hbgu.parent_id
     , hbgu.jid
  FROM hms_bbr_group_user hbgu
 WHERE hbgu.group_id = 113
 GROUP BY hbgu.id
;

For both of the above, the result is:

The setup:

CREATE TABLE public.hms_bbr_group_user
(
    id integer NOT NULL,
    group_id integer,
    parent_id integer,
    jid character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT hms_bbr_group_user_pkey PRIMARY KEY (id)
);

INSERT INTO hms_bbr_group_user VALUES
   (1, 113, 113, 'jhadmin')
 , (2, 113, 114, 'jhadmin')
;

CREATE TABLE public.hms_doctor
(
    jid character varying(50) COLLATE pg_catalog."default" NOT NULL,
    name character varying(100) COLLATE pg_catalog."default" NOT NULL
);

INSERT INTO hms_doctor VALUES
    ('jhadmin', 'Doctor jhadmin')
;

CodePudding user response：

It is not an issue, you made the group by id and name (GROUP BY hbgu.id, d.name) so we can see that you have 2 groups first is when id =1 and second when id = 2 and under the parent_id column got the max value of parent_id in the group when id is 1 and the second one when id is 2 if you will remove from the group by hbgu.id field you will get one row and parent_id will be 114. Just MAX (hbgu.parent_id) AS parent_id is not a condition, this means to choose the maximum value of selected parent_id by the condition which you had set