Re: Group By aggregate string function - Mailing list pgsql-sql

Correction. I had two typos.  I did not want to confuse someone.

PostgreSQL 10.x

Below is my situation.  I need some kind of aggregate string function that when it finds multiple string values it will order them based on a preferred preference.  Example:  “admin”, then “manager” then “…”. 

Table T

fk_id int – foreign key

user_id  text

role text  - possible values could be “admin” and “manager”

Primary key (fk_id, user_id, role)

Sample data:

1. lance  admin

1     lance manager

87   bob   manager

98   tom admin

104 tom manager

SELECT fk_id, user_id, some-aggregate-string-function(role, “admin”, “manager”)  FROM T  WHERE user_id = ‘lance’ GROUP BY fk_id, user_id;

When selecting data if there are multiple rows within the group by then aggregate the result based on a priority for role of “admin” first, “manager” second, etc.

Expected Result:

1 lance admin

Ignores the second record with lance in it because the first record contained admin.

THANKS!

PostgreSQL 10.x

Below is my situation.  I need some time of aggregate string function that when it finds multiple string values it will order them based on a preferred preference.  Example:  “admin”, then “manager” then “…”. 

Table T

fk_id int – foreign key

user_id  text

role text  - possible values could be “admin” and “manager”

Primary key (fk_id, user_id, role)

Sample data:

1. lance  admin

1     lance manager

87   bob   manager

98   tom admin

104 tom manager

SELECT fk_id, user_id, some-aggregate-string-function(role, “admin”, “manager”)  FROM T  WHERE user_id = ‘lance’ GROUP BY fk_id, user_id;

When selecting data if there are multiple rows within the group by then aggregate the result based on a priority for role of “admin” first, “manager” second, etc.

Expected Result:

1 lance admin

Ignores the second record with lance in it because it contains admin.

THANKS!

LANCE CAMPBELL

Software Architect

Web Services

Public Affairs

Contact the Webtools Team

217.333.0382

lance@illinois.edu

Under the Illinois Freedom of Information Act any written communication to or from university employees regarding university business is a public record and may be subject to public disclosure.