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