From: Campbell, Lance <lance@illinois.edu>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Group By aggregate string function
Date: Thu, 21 Feb 2019 19:02:27 +0000
Message-ID: <730D7ADD-A08D-4EA2-A4B0-7C7EF7ABC4CB@illinois.edu> (raw)
In-Reply-To: <04E1F4B6-7092-4338-B97D-EC495C5F1B1E@illinois.edu>
References: <04E1F4B6-7092-4338-B97D-EC495C5F1B1E@illinois.edu>
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!
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:
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@illinois.edu<mailto:lance@illinois.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.
Attachments:
[image/png] image001.png (2.5K, ../730D7ADD-A08D-4EA2-A4B0-7C7EF7ABC4CB@illinois.edu/3-image001.png)
download | view image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lance@illinois.edu
Subject: Re: Group By aggregate string function
In-Reply-To: <730D7ADD-A08D-4EA2-A4B0-7C7EF7ABC4CB@illinois.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox