Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gwuWb-0003OW-9L for pgsql-sql@arkaria.postgresql.org; Thu, 21 Feb 2019 20:00:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gwuWZ-0001ou-V6 for pgsql-sql@arkaria.postgresql.org; Thu, 21 Feb 2019 20:00:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gwuWZ-0001oj-B8 for pgsql-sql@lists.postgresql.org; Thu, 21 Feb 2019 20:00:51 +0000 Received: from mail.inqbus.de ([164.177.165.236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gwuWV-0005mx-8E for pgsql-sql@lists.postgresql.org; Thu, 21 Feb 2019 20:00:50 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=stb-datenservice.de; s=20160215; h=Content-Type:In-Reply-To:MIME-Version: Date:Message-ID:From:References:To:Subject:Sender:Reply-To:Cc: Content-Transfer-Encoding:Content-ID:Content-Description:Resent-Date: Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=oPD5//Ecyfi0Ez/9wm+5m8YoyL2gJpRoIfbmQZQAop8=; b=DEV7uvcweHfQqilx0UE2znv6Q WZ7cp5ubwAwDnnq/QdkXfYkjLlvBgh5Vb98YilfuUG89tbPA6CJPzQdL+okpTPINqAWZsbsRu5nur 6JjnTR+JYwzIyXK3N+JHpMBOd4KlQ9dJQ2MuA0vu93XUcZaN9lVLPgGwrwSkXOhHAar0Y=; Received: from x590c7c0b.dyn.telefonica.de ([89.12.124.11]:51699 helo=[192.168.178.20]) by mail.inqbus.de with esmtpa (Exim 4.89) (envelope-from ) id 1gwuWQ-0004yD-NY for pgsql-sql@lists.postgresql.org; Thu, 21 Feb 2019 21:00:44 +0100 Subject: Re: Group By aggregate string function To: pgsql-sql@lists.postgresql.org References: <04E1F4B6-7092-4338-B97D-EC495C5F1B1E@illinois.edu> <730D7ADD-A08D-4EA2-A4B0-7C7EF7ABC4CB@illinois.edu> From: =?UTF-8?Q?Martin_St=c3=b6cker?= Message-ID: Date: Thu, 21 Feb 2019 21:06:15 +0100 User-Agent: Mozilla/5.0 (Windows NT 6.0; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <730D7ADD-A08D-4EA2-A4B0-7C7EF7ABC4CB@illinois.edu> Content-Type: multipart/alternative; boundary="------------AF68DF1C32615156BC99D312" Content-Language: de List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------AF68DF1C32615156BC99D312 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi Lance 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 Am 21.02.2019 um 20:02 schrieb Campbell, Lance: > > 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 > *Date: *Thursday, February 21, 2019 at 1:00 PM > *To: *"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 * > > /Software Architect/ > > Web Services > > Public Affairs > > Contact the Webtools Team > > 217.333.0382 > > lance@illinois.edu > > /var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png > > > /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./ > --------------AF68DF1C32615156BC99D312 Content-Type: multipart/related; boundary="------------259B116BB62298B9FE088C8D" --------------259B116BB62298B9FE088C8D Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Hi Lance

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

Am 21.02.2019 um 20:02 schrieb Campbell, Lance:

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

Software Architect

 

Web Services

Public Affairs

Contact the Webtools Team

217.333.0382

lance@illinois.edu

 

 

/var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png

 

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.

 


--------------259B116BB62298B9FE088C8D Content-Type: image/png; name="image001.png" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="image001.png" iVBORw0KGgoAAAANSUhEUgAAAHcAAAAVCAYAAAELAkFNAAAAAXNSR0IArs4c6QAAAARnQU1B AACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAm/SURBVFhH7Vl9iB1XFX8bo8XmY/Pe zNt0ZWPK5u2+mXnZDUlqIpJ2bdmP9+bOvE2abpMSQQVNK1j9S0SkhCIqftAiBautoAUxUhSk UCEQCVSrbdraNlC01dRu0tjW3STbbTTU1Yy/35l7J/Pem32J+WhSzQ8uc++5n+ece885906u y60921cZezF3rnjDL0eSWb/zvQXH38us5aiHhQYUXPVHnc0VV2+JbDeoFZzwrtzawdFTrzd2 3mdX6pHlhdEy118pdA0bNPm6yrdXb45yXHJ/pXpuyz6kKvGsgOX5t+uswK6MR5ar3uQszJPW tTpcblcwKzHl90cH1YAUrP7qZ7hk5vllp4KjXpNBytUdtleP2BksvVIc2BoJzxyAfJtBBOCf g1EGmiKwyurnOpsTvtPfgS3yRZ+bKR8jI1kt5eSonWxbHLg5nodsJ9oy4MQQgdGcQVqDRgyg Hba9YNZy/VMxPeacoGbjiYMay8uXjyyy3CBK9td5KexcYCZlyjsjqzX54uPZ6hrR76s1J8pd u32jJkNvsRgTfQB2Kg/xfoVf6rXoDS1mvuD6R/hlH9lUXniIZSvZdOPQO85MuV5mOceJ6+s+ Oi0FDaMn6kgIgJ3KY7vfzS83Cw+f0Bx/Eukx5gnocka+5iDi63kT72NecL4TS9kLIpnYVY+z TEAqR+WrJyZsT92PTbjnjKLmgFwExDhLUcdnFGJMiZrfoheWWkVdP8hyg6iRutcHV1+6zfXh wZGZY6o/mm5Kc/VSlFux/TrdLI0O4a5UXcqCtaL+AZalpgmk8wzrYoKl3mgh3Yd52xnr1kUB ac1Gmyh6waaCpyZoDzQpxsTEe9hHpHkaHVYlCLGGQV0+jUkYL6qaBmxs7U1TmtwKGDSq2dh1 Grb0/kuDezFt6Ay4L6lqXYwPhquwddL7GDTsVeYp3PgkhtHi/sAmDQfmh+yD+fezrN2AuD+W 2R+ucZb5a0rVIvyLbLcEtNaG4eaD1YCLxbCjDuXd2j3CRLm2Nc0w6may5gDTD5hxmhkueqMl zi+uiuewUhcvkuD8NLw56lwzvqxzQOUlrRxfxjpOCIf8i4SOlMvtWjAPw4d1UYwAGTcM8wiI hjHvosEtXaTFVkg0/GuWmxlG3fO2V7uLeUI0TpN6Qc5wpboqnTrLY9eyju0a6zavyg0NLcw6 w83nlWNknWHb9W8olIOtPT23vF+TYmSfYTit+jACkOt1ka64mljLdOr21DsXD7xTeDPoa9Es 06c23JB5JinxZs10ayOSBrdXul0apBcGqj1xfmJxczur/JElmtYRU2LwOGC7boPvS3ynQVef 6m0ep1hRaxhalkrVq4TwQm0wernmyrll+hvS76uD0Y6Nw5nMwmtPpmMeOmCrHNyniwkKZX+3 cc7N4NlE/TPM513/Vjr5ghe8JJUA5viYBACp8ATn8nhseCSGkpi42F/dpKt5STnJ86mLmEMb KtDEWHmhWOqcOzC6n9oks7MBY+lvLJGKDGAhL7e6DPUdXUwAo/Dj9ORp0MqiXlwI/agYJSSE 1XeQZnlqBxdomAUjR0Qg5eDLLNMGCBOyjl0LpI2j3jLGDxeMemy0VB/LxXLwhWQ3rBscfbqB 2UV3iOXLwsVhFvGg6/+Di5ULRROzHAftn2PegEcnjiPVD1hOM2s7wZAW4CQCjQ+RluBSM8sw Xujcmqxz1bBhdomz2eJ2LHj+nWyThrg4Vx1gPs0sYbvhk6ynQLil4c6+KhWXA7Pd3cHVsj3B GDUtAjCaxW0bc7zAvEG+d6JThOD632e5mVmDWMvB28la3IGx8zqzuEh/zwQP9KMgdxhmG4IO JPbJYtb4Tox/H7dgmlmUp2Sre/GtJO8FHyRjeh1isdPM0t9iZ/zbxODo/6DUXQhrLFaPFg+J URWDDlpjc5UyiWX2abbGXEg6UIBlnpXFaWYJXK1OiNEhk/hKtNVfTc5j2hpTgKynwEhjPzB8 4IL42YZISep2LRA/m6YjdQ2oXtMnw882+NQsGo1S0atPtBgdIMvP0hpDOeMm1Pz/iqCuGxw5 +VflRod9L5o8izQTOtEnN94Y5XpubZHuFVxm4OXn7zi6s/q4vqZtVTqRNoU0g/oIl6KdG66f 72LUCj5oOv5LsBdzdmXLHG5gn9Y1Aqvs74F3mSuiDkfsXk1uC7R7SMZCP+R3a3Jb2J6/i3PY 3jj7/E6TBbBpt8RjBXNWpX4UHmudrmoAbONtbEdewNPBtA1No1iqrrIq4QMY7w2xz3RaTLCZ oB2y3fq3F/WOZHp4ubU66rjlhrJWmKKqrkoAB7gdNvv5xGYjSYzlhgexxm/BR4hJzBUd/5c9 Ff9XeVzJR9fedBzKTJ67TToB5d++YdPJpeXgsRUVf2+XF+yzymPx8+WZQOXSQwuTjGsaH+/h dfeScXrgrBAlC7Enh+Ojc8l4G8kCHNDdnIOCMJ7eQDw+x9KReuwgaz/R1QkkmKWS6Ez5zNCk 3IJTG0mcLcMjGdOfxhp/Awe53zhJziHhkhu+lXdvrOjuAiqXkUOiOP00QdAnxVEFnTmcquMf Q2j1CMbbgzRNmcjaXfVivne4U3eLwfiRD1Q8wUaxfLdheCWPVdfcWdRNzx7vOuUyZGOC8Lzw n7ZbXaubtVVuoaw+LxEPFYc2EPrndFULMMePzHz85t1aosB2ygX948I31+YGM2yrqxK0PMka nFG5bS4L8+JdpNzYbKoDCGs/wXYUvpwEzMd2tuPfJu2alEuBos3rEtKSj3LwNdLbAe0fjceS eX6ryW2VmytVr+L6kk0koTFlKsr+M0Lu79rO6HrduhFXlEul8aV3aCHDfZi3p2QOCs9RR3GH uRf5U9nKVYlyYZ6/KQO3Ac1pSrnJP622yj2NBYwT0GY3lHo4ka22OvydjA0W6rYxLrlZdtU9 mtwWjcr1f6rJbfHfKDd9AbXd2jaZh4ITsx2fmGazzJevtFlG/Rd1VQugvPtjZRgTroZ11RmV 24n7XvLC1oQCgkaRC/qCxz9dNgEVGWXAgbZTyE+nk+0F02j3B16IdR9RLvtAiG9n9YF/nMZ4 R8xlGrRzUq5gaGghxnrarDNLuQRPiyiE9SagQtCDPo9j3CegtBPJGPINjptnUYM2yu0ATw93 rZmQ06n5n4Gi9+H7M/D0XOyL6Uq2gkf12cvmKgTm5002rgUY41X+QZA++iqU1TZJuNZQSPnK +Ar2OZurEK4o7PuXFuVqIPDZZlXGT5IXrGfeq1Chz3ehnIewhqOiIG4cJgrfC18puOHX9Rte C6hcrGHeq1DnyqFlWO+XoOADUOS/ZHxE3uIq3PAYxn/Q/P698ojxP4tc7j8gMqgPv+E4KQAA AABJRU5ErkJgggAAAAAAAA== --------------259B116BB62298B9FE088C8D-- --------------AF68DF1C32615156BC99D312--