From: | "Campbell, Lance" <lance(at)illinois(dot)edu> |
---|---|
To: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Group By aggregate string function |
Date: | 2019-02-21 18:59:18 |
Message-ID: | 04E1F4B6-7092-4338-B97D-EC495C5F1B1E@illinois.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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<https://directory.illinois.edu/person/lance>
Software Architect
Web Services<https://webtools.illinois.edu/>
Public Affairs<https://publicaffairs.illinois.edu/>
Contact the Webtools Team<https://go.illinois.edu/contactUs>
217.333.0382
lance(at)illinois(dot)edu<mailto:lance(at)illinois(dot)edu>
[/var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png]<http://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.
From | Date | Subject | |
---|---|---|---|
Next Message | Campbell, Lance | 2019-02-21 19:02:27 | Re: Group By aggregate string function |
Previous Message | Michael Lewis | 2019-02-19 17:11:33 | Re: Postgresql RDS DB Latency Chossing Hash join Plan |