How to select specific value from a table that has multiple values for a record in primary key table in PostgreSQL? I have three tables as follows (with sample data):  

CREATE TABLE users (
  id int PRIMARY KEY,
  login varchar(20),
  organization varchar(20)
);
CREATE TABLE rights (
  id int PRIMARY KEY,
  name varchar(20)
);
CREATE TABLE user_rights_rel (
  uid int REFERENCES users,
  rid int REFERENCES rights,
  PRIMARY KEY (uid, rid)
);

-- 1-5  'A'
-- 6-10 'B'
INSERT INTO users VALUES
  (1,  'Smith',   'A'),
  (2,  'Jane',    'A'),
  (3,  'Katrin',  'A'),
  (4,  'Todd',    'A'),
  (5,  'Ervine',  'A'),
  (6,  'David',   'B'),
  (7,  'White',   'B'),
  (8,  'Anderson','B'),
  (9,  'Randi',   'B'),
  (10, 'Polak',   'B');  

INSERT INTO rights values
  (9,'Contact'),
  (62,'Download'),
  (59,'Reviewer'),
  (32,'High Reviewer'),
  (64,'Read Only'),
  (66,'Data Entry');  


INSERT INTO user_rights_rel VALUES
  (1,9),(1,64),(1,59),(1,62),
  (2,64),(2,59),(2,62),(2,66),
  (3,66),(3,62),(3,9),
  (4,66),(4,64),(4,9),
  (5,62),(5,64),(5,9),
  (6,9),(6,64),(6,62),
  (7,64),(7,62),
  (8,66),(8,9),
  (9,66),(9,64),(9,9),(9,32),
  (10,62),(10,64),(10,9);  


Now I want to write a query based on the below criteria:  


If a user has Reviewer or High Reviewer right along with other rights, it should be counted as Reviewer.  
If a user has Data Entry right along with the other rights(except Reviewer and High Reviewer), it should be counted as Data Entry User.  
If a user has Read Only right along with the other rights(except Reviewer, High Reviewer and Data Entry), it should be counted as Read Only User.  


As per the above criteria, my desired output is:  

Organization        Reviewer     Data Entry        Read Only
   A                    2            2                 1
   B                    1            1                 3  


Organization A has 2 Reviewers (Users 1 and 2). If a user has Reviewer right then we count it as Reviewer user and we ignore other rights.  

Organization A has 2 Data Entry users (Users 3 and 4). If a user does not have Reviewer and High Reviewer rights then we look for Data Entry right, if it has Data Entry right then we count it as Data Entry user and ignore other rights.  

Organization A has 1 Read Only user (User 5). If a user does not have Reviewer,High Reviewer and Data Entry rights, then we look for Read Only right, if it has Read Only right then we count it as Read Only user and ignore other rights.  

The priority should be as follows:
1. Reviewer (if a user has it, count him/her as Reviewer and ignore other rights).
2. Data Entry (if a user has it, count him/her as Data Entry and ignore other rights).
3. Read Only (If a user has it, count him/her as Read Only and ignore other rights.)  

Any help is appreciated in advance.
Single pass using DISTINCT ON

We can use DISTINCT ON in subselect to get the users highest priority rights. Then we need only count the rights by those groups.

It's important to note, we're joining to rights here. We needn't do that. You could adopt the method of simplicity used in @JackDouglas's answer to compare on ints. It saves a lot of time by not joining but because we only do one table scan we're still twice as fast. In production, you may want to skip the join on rights.

SELECT
  organization,
  count(rightgrp) FILTER (WHERE rightgrp = 1) AS "Reviewer",
  count(rightgrp) FILTER (WHERE rightgrp = 2) AS "Data Entry User",
  count(rightgrp) FILTER (WHERE rightgrp = 3) AS "Read Only User"
FROM (
  SELECT DISTINCT ON (organization, uid)
    organization,
    uid,
    CASE
      WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
      WHEN rights.name IN ('Data Entry') THEN 2
      ELSE 3
    END AS rightgrp
  FROM rights
  JOIN user_rights_rel
    ON rid = rights.id
  JOIN users
    ON uid = users.id
  ORDER BY 1, 2, 3
) AS t
GROUP BY organization
ORDER BY organization;

 organization | Reviewer | Data Entry User | Read Only User 
--------------+----------+-----------------+----------------
 A            |        2 |               2 |              1
 B            |        1 |               1 |              3
(2 rows)


Using the tablefunc extension and crosstab

I wouldn't use this method, it's too sledge-hammery for what you need.

Here is a method that users crosstab for display, though it doesn't have to,

SELECT *
FROM crosstab($$
  SELECT organization, rightgrp, count(*)
  FROM (
    SELECT DISTINCT ON (organization, uid)
      organization,
      uid,
      CASE
        WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
        WHEN rights.name IN ('Data Entry') THEN 2
        ELSE 3
      END AS rightgrp
    FROM rights
    JOIN user_rights_rel
      ON rid = rights.id
    JOIN users
      ON uid = users.id
    ORDER BY 1, 2, 3
  ) AS t
  GROUP BY organization, rightgrp
  ORDER BY 1,2
$$, $$
  SELECT * FROM (VALUES (1),(2),(3)) AS t(cat)
$$
) AS ct(organization text, "Reviewer" int, "Data Entry User" int, "Read Only User" int);


First, we optimize the table read to a single pass which we select only the highest-privilege rights for the (organization,user).

SELECT DISTINCT ON (organization, uid)
    organization,
    uid,
    CASE
      WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
      WHEN rights.name IN ('Data Entry') THEN 2
      ELSE 3
    END AS rightgrp
  FROM rights
  JOIN user_rights_rel
    ON rid = rights.id
  JOIN users
    ON uid = users.id
  ORDER BY 1, 2, 3;

 organization | uid | rightgrp 
--------------+-----+----------
 A            |   1 |        1
 A            |   2 |        1
 A            |   3 |        2
 A            |   4 |        2
 A            |   5 |        3
 B            |   6 |        3
 B            |   7 |        3
 B            |   8 |        2
 B            |   9 |        1
 B            |  10 |        3
(10 rows)


Then we simply GROUP BY organization, rightgrp and select the count, like this

SELECT DISTINCT ON (organization, uid)
  organization,
  uid,
  CASE
    WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
    WHEN rights.name IN ('Data Entry') THEN 2 
    ELSE 3 
  END AS rightgrp
FROM rights
JOIN user_rights_rel
  ON rid = rights.id
JOIN users
  ON uid = users.id
ORDER BY 1, 2, 3 

 organization | rightgrp | count 
--------------+----------+-------
 A            |        1 |     2
 A            |        2 |     2
 A            |        3 |     1
 B            |        1 |     1
 B            |        2 |     1
 B            |        3 |     3
(6 rows)


From there we can feed this to pivot using PostgreSQL's tablefunc extension which provides crosstab. We can generate the pivot manually though using crafty SELECT/FILTER. Keep in mind right #1 is the highest priority

 organization | Reviewer | Data Entry User | Read Only User 
--------------+----------+-----------------+----------------
 A            |        2 |               2 |              1
 B            |        1 |               1 |              3


If you want to generate it without crosstab..