0

I have following rows in my user table as

enter image description here

Now I want to get user having all accesstype as view,edit and share. So I need to get two rows out of this as

enter image description here

How can I achieve this using postgresql statement.

6
  • 1
    Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. Jul 16 at 11:22
  • 1
    What if a user had more types than just those three? Or to put it in another way: do you want those users who have exactly those three access types, or at least those three access types? Jul 16 at 11:23
  • yes i want those users who have exactly those three access types Jul 16 at 12:37
  • 1
    Unrelated but: this is a rather questionable design. Why is the user name and age repeated in each row? I would have expected the id column to be a foreign key that points to a "users" table that stores the name and other user attributes - but age is not an attribute that should be stored in a "users" table as that value essentially changes every day. Jul 16 at 13:31
  • exactly we should not have these repeated id,name and age in a table but i put a scenario where i get these rows as a result of my sub query and again i want to get the data as i described. Jul 16 at 13:44
3

To get the rows that have exactly those three access types, you can aggregate them into an array:

select id, name, age
from the_table
group by id, name, age
having array_agg(accesstype order by accesstype) = array['edit', 'share', 'view']

Note that the order of the elements in the array[...] expression matters for the = operator used because array['a', 'b'] is not equal to array['b', 'a'].

Alternatively you can use bool_and()

select id, name, age
from the_table
group by id, name, age
having bool_and(accesstype in ('edit', 'share', 'view'))
   and count(distinct accesstype) = 3
3
  • ya i tried solution 1 and its working fine for me. Thank you so much...... i will try second solution as well. Jul 16 at 13:49
  • Hi @a-horse-with-no-name If i need the rows which have minimum edit,share and view access. Not exactly edit,view and share access, then how should i use this query? can you suggest the changes i need to do in above query? Aug 6 at 7:06
  • okay so I tried this way now : select id, name, age from the_table group by id, name, age having bool_or(accesstype in ('edit', 'share', 'view')) and count(distinct accesstype) >= 3 and its working fine for me Aug 9 at 11:59
2

Something like:

SELECT id, name, age
FROM (
    SELECT id, name, age
         , count(distinct accesstype) over (partition by id) as cnt
    FROM T
    WHERE accesstype IN ('view','edit','share')
) AS U
WHERE cnt = 3

Assuming id, accesstype is unique you can change that to:

SELECT id, name, age
FROM (
    SELECT distinct id, name, age
         , count(accesstype) over (partition by id) as cnt
    FROM T
    WHERE accesstype IN ('view','edit','share')
) AS U
WHERE cnt = 3

Untested since you did not provide a Fiddle or similar. For future posts please include something that makes it easy to reproduce your scenario.

You may also consider normalizing your tables into a separate relation for access type. Also, age is something you should derive from for example birth date. As of now, you will have to update this attribute on a regular basis (not clear on how you know when raj becomes 17 years old)

3
  • if I try solution 1 I get error as ERROR: DISTINCT is not implemented for window functions but if I try solution 2 i get all row not the distinct rows Jul 16 at 13:47
  • 1
    @RajeshKumar no, you would not get all rows. You did not run the query exactly as suggested. Jul 16 at 15:28
  • @Lennart Okay will try again Aug 6 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.