Group By aggregate string function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Group By aggregate string function

Campbell, Lance-2

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

[hidden email]

 

 

/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.

 

Reply | Threaded
Open this post in threaded view
|

Re: Group By aggregate string function

Campbell, Lance-2

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 <[hidden email]>
Date: Thursday, February 21, 2019 at 1:00 PM
To: "[hidden email]" <[hidden email]>
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

[hidden email]

 

 

/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.

 

Reply | Threaded
Open this post in threaded view
|

Re: Group By aggregate string function

David G Johnston
On Thu, Feb 21, 2019 at 12:02 PM Campbell, Lance <[hidden email]> wrote:

Expected Result:

1 lance admin

 

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


Ignoring <> Grouping

Grouping:
SELECT x, array_agg(DISTINCT y ORDER BY y)
FROM (VALUES ('a',1),('a',2),('b',1),('c',2)) vals (x,y)
GROUP BY x;

Ignoring:
SELECT DISTINCT ON (x) x, y
FROM (VALUES ('a',1),('a',2),('b',1),('c',2)) vals (x,y)
ORDER BY x, y;
 
David J.

image001.png (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Group By aggregate string function

MS (direkt)
In reply to this post by Campbell, Lance-2
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 [hidden email]
Date: Thursday, February 21, 2019 at 1:00 PM
To: [hidden email] [hidden email]
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

[hidden email]

 

 

/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.