0

I have a table with 2 columns

organization_id | user_name
1 | abc
1 | xyz
2 | bhi
2 | ipq
2 | sko
3 | ask
...

Each organization could have any number of users ranging from 1 to 100, 2000 and so on. I wanted to show them in grafana in a table as following:

organization_id | user_name
1 | abc, xyz
2 | bhi, ipq, sko
3 | ask

Since there could be many users I want to show any 10 users belonging to same organization.

The database here is timescale db, the table is also a time series table showing when user was registered

Jan Garaj
  • 25,598
  • 3
  • 38
  • 59
Kush Rohra
  • 15
  • 5
  • @a_horse_with_no_name do you know the answer to this one – Kush Rohra Apr 07 '22 at 08:57
  • 1
    Does this answer your question? [How to concatenate strings of a string field in a PostgreSQL 'group by' query?](https://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query) – Jan Garaj Apr 07 '22 at 14:37

1 Answers1

1

If I understand rightly that you want 10 users per organisation you can use the query below.
I have added group by in the CTE to avoid returning duplicate user_name's.
In the test schema there are duplicate values of 'pqr' for organisation 2 but this username is only returned once even though there are less then 10 user_name's for 2
test schema db Fiddle here

With topTen as
(Select
  Organisation_id,
  User_name,
  Rank() over (
    partition by organisation_id 
    order by user_name) rn
From table_name 
group by 
  Organisation_id,
  user_name)
Select
  Organisation_id,
  String_agg(user_name,',') users
From topTen
Where rn <= 10
group by Organisation_id;

organisation_id | users                                  
--------------: | :--------------------------------------
              1 | abc,abk,def,ghi,jkl,mno,pqr,rst,ruk,stu
              2 | abk,pqr,rst,ruk,stu,vwx

Another alternative which may be useful. If you remove the where and put the following after From topTen you will get all the distinct user_names, 10 per row.

group by Organisation_id,rn/10
order by Organisation_id,rn/10;

db<>fiddle here

  • This works, Thanks. There is one more follow up, What if their were duplicate user_names. We although have unique user_id to differentiate them. So if I wanted to get any 10 unique user names for all the organizations – Kush Rohra Apr 08 '22 at 10:12