Thread: Group By aggregate string function
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:
- 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!
Software Architect
217.333.0382
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.
Attachment
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:
- 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!
From: Lance Campbell <lance@illinois.edu>
Date: Thursday, February 21, 2019 at 1:00 PM
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Group By aggregate string function
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:
- 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!
Software Architect
217.333.0382
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.
Attachment
Attachment
first of all you should check your data-model.
If there are different roles or jobs in a certain order, you should better bring these jobs in theier own table.
So it is easy to have them ordered.
But you can do some tricks to treat them as array elements
Not nice but it works
create table staff(id integer, user text, job text);
insert some data ...
select id, usr, ar[pos] from (select id, usr, min(array_position(array['admin', 'manager'], job)) as pos, array['admin', 'manager'] as ar from staff where usr='lance' group by id, usr, ar)
as test;
id | usr | ar
----+-------+-------
1 | lance | admin
Regards Martin
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:
- 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!
From: Lance Campbell <lance@illinois.edu>
Date: Thursday, February 21, 2019 at 1:00 PM
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Group By aggregate string function
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:
- 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!
Software Architect
217.333.0382
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.
