Site icon Parenting Pro

SQL: How to I create another “maximum” categorical value column based on multiple partitions?

William Mercado

I have this table (table_2), which basically provides patient info and the department they visited on specific dates –

Patient_ID department service_dte birth_dte count
12345 cardiac 1/2/21 6/18/78 5
12345 cardiac 8/20/21 6/18/78 5
12345 cardiac 10/28/21 6/18/78 5
12345 ortho 1/14/21 6/18/78 2
12345 ortho 7/7/21 6/18/78 2
12345 cardiac 8/20/21 6/18/78 5
12345 cardiac 4/19/21 6/18/78 5
12345 obgyn 4/1/21 6/18/78 1
78645 neuro 5/1/21 7/18/87 2
78645 neuro 7/7/21 7/18/87 2
78645 ent 7/7/21 7/18/87 1
32423 gastro 11/7/21 3/12/57 1

I would like my desired output to produce an additional column (max) that provides highest repeated “department” per “Patient_ID”, while keeping all the columns as they are, similar to below table –

Patient_ID department service_dte birth_dte count max
12345 cardiac 1/2/21 6/18/78 5 cardiac
12345 cardiac 8/20/21 6/18/78 5 cardiac
12345 cardiac 10/28/21 6/18/78 5 cardiac
12345 ortho 1/14/21 6/18/78 2 cardiac
12345 ortho 7/7/21 6/18/78 2 cardiac
12345 cardiac 8/20/21 6/18/78 5 cardiac
12345 cardiac 4/19/21 6/18/78 5 cardiac
12345 obgyn 4/1/21 6/18/78 1 cardiac
78645 neuro 5/1/21 7/18/87 2 neuro
78645 neuro 7/7/21 7/18/87 2 neuro
78645 ent 7/7/21 7/18/87 1 neuro
32423 gastro 11/7/21 3/12/57 1 gastro

I tried below code; that provided me the count of department a patient visited in the above table –

select *
     , count(department) OVER (PARTITION BY patient_id, department) AS count
FROM table_1

however, it is not giving me desired value for patient 12345’s max column, which should be cardiac. Instead of cardiac, I am getting ortho. Please kindly assist. This is the code I am trying –

select *
   , max(department) OVER (PARTITION BY patient_id) AS max
FROM table_2

Please kindly assist with an optimal way I can get the maximum column and advise on what I am or might be doing wrong.

Thank you.

Exit mobile version