Group By aggregate string function

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Group By aggregate string function
4+ messages / 3 participants
[nested] [flat]

* Group By aggregate string function
@ 2019-02-21 18:59 Campbell, Lance <lance@illinois.edu>
  2019-02-21 19:02 ` Re: Group By aggregate string function Campbell, Lance <lance@illinois.edu>
  0 siblings, 1 reply; 4+ messages in thread

From: Campbell, Lance @ 2019-02-21 18:59 UTC (permalink / raw)
  To: 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@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:

image001.png (image/png, 2.5K) download
  view image

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Group By aggregate string function
  2019-02-21 18:59 Group By aggregate string function Campbell, Lance <lance@illinois.edu>
@ 2019-02-21 19:02 ` Campbell, Lance <lance@illinois.edu>
  2019-02-21 19:53   ` Re: Group By aggregate string function David G. Johnston <david.g.johnston@gmail.com>
  2019-02-21 20:06   ` Re: Group By aggregate string function Martin Stöcker <martin.stoecker@stb-datenservice.de>
  0 siblings, 2 replies; 4+ messages in thread

From: Campbell, Lance @ 2019-02-21 19:02 UTC (permalink / raw)
  To: pgsql-sql

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:

image001.png (image/png, 2.5K) download
  view image

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Group By aggregate string function
  2019-02-21 18:59 Group By aggregate string function Campbell, Lance <lance@illinois.edu>
  2019-02-21 19:02 ` Re: Group By aggregate string function Campbell, Lance <lance@illinois.edu>
@ 2019-02-21 19:53   ` David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: David G. Johnston @ 2019-02-21 19:53 UTC (permalink / raw)
  To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-sql

On Thu, Feb 21, 2019 at 12:02 PM Campbell, Lance <lance@illinois.edu> 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.

Attachments:

image001.png (image/png, 2.5K) download
  view image

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Group By aggregate string function
  2019-02-21 18:59 Group By aggregate string function Campbell, Lance <lance@illinois.edu>
  2019-02-21 19:02 ` Re: Group By aggregate string function Campbell, Lance <lance@illinois.edu>
@ 2019-02-21 20:06   ` Martin Stöcker <martin.stoecker@stb-datenservice.de>
  1 sibling, 0 replies; 4+ messages in thread

From: Martin Stöcker @ 2019-02-21 20:06 UTC (permalink / raw)
  To: pgsql-sql@lists.postgresql.org

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 <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:

image001.png (image/png, 2.5K) download
  view image

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2019-02-21 20:06 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-21 18:59 Group By aggregate string function Campbell, Lance <lance@illinois.edu>
2019-02-21 19:02 ` Campbell, Lance <lance@illinois.edu>
2019-02-21 19:53   ` David G. Johnston <david.g.johnston@gmail.com>
2019-02-21 20:06   ` Martin Stöcker <martin.stoecker@stb-datenservice.de>

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