Remove row duplicates and get the highest value with PostgreSQL

Question

With the following query

SELECT
  users.id,
  users.username,
  users.email,
  groups.permission
FROM users
INNER JOIN memberships ON users.id = memberships.user_id
INNER JOIN groups ON memberships.group_id = groups.id
INNER JOIN groupings ON groups.id = groupings.group_id
WHERE groupings.repo_id = 1
GROUP BY users.id, groups.permission
ORDER BY users.id

I get the following rows:

----+---------------------------+-----------------------------------------+------------
 id |         username          |                  email                  | permission 
----+---------------------------+-----------------------------------------+------------
  2 | viva_leuschke0            | viva_leuschke0@ritchiealtenwerth.org    | 1
  2 | viva_leuschke0            | viva_leuschke0@ritchiealtenwerth.org    | 2
  3 | loyce_herman1             | herman1.loyce@bednar.com                | 1
  3 | loyce_herman1             | herman1.loyce@bednar.com                | 3
  4 | verona_vandervort2        | verona.vandervort2@mante.biz            | 1
  4 | verona_vandervort2        | verona.vandervort2@mante.biz            | 2
  4 | verona_vandervort2        | verona.vandervort2@mante.biz            | 3
  5 | bruen3_ms_hans            | ms.bruen3.hans@bechtelar.net            | 1
  5 | bruen3_ms_hans            | ms.bruen3.hans@bechtelar.net            | 2
  5 | bruen3_ms_hans            | ms.bruen3.hans@bechtelar.net            | 3
----+---------------------------+-----------------------------------------+------------

The question is: how can I tweak this query to return unique rows, filtering by the highest permission value? Something like:

----+---------------------------+-----------------------------------------+------------
 id |         username          |                  email                  | permission 
----+---------------------------+-----------------------------------------+------------
  2 | viva_leuschke0            | viva_leuschke0@ritchiealtenwerth.org    | 2
  3 | loyce_herman1             | herman1.loyce@bednar.com                | 3
  4 | verona_vandervort2        | verona.vandervort2@mante.biz            | 3
  5 | bruen3_ms_hans            | ms.bruen3.hans@bechtelar.net            | 3
----+---------------------------+-----------------------------------------+------------

I'm using PostgreSQL 9.1.

UPDATE: By using the DISTINCT ON clause I was able to get what I wanted.

SELECT DISTINCT ON(users.id)
  users.id,
  users.username,
  users.email,
  groups.permission
FROM users
INNER JOIN memberships ON users.id = memberships.user_id
INNER JOIN groups ON memberships.group_id = groups.id
INNER JOIN groupings ON groups.id = groupings.group_id
WHERE groupings.repo_id = 1
GROUP BY users.id, groups.permission
ORDER BY 
  users.id ASC, 
  groups.permission DESC

Is this the best way of doing it?

I suppose you post the (valid!) answer you found as *answer* and accept it. That's the recommended way: you can answer your own question. — Erwin Brandstetter, Dec 16 '12 at 04:35
Hehehehehe... actually, I was asking if the query was the best one to the job. — Nando Vieira, Dec 16 '12 at 04:56
Seems like I missed two things. Your added question and the imperfection in your added query. Nice, clean question, btw. — Erwin Brandstetter, Dec 16 '12 at 05:23

score 1 · Accepted Answer · edited May 23 '17 at 12:29

On closer inspection, your query can (and should) be improved:


SELECT DISTINCT ON (u.id)
      ,u.id
      ,u.username
      ,u.email
      ,g.permission
FROM   users       u
JOIN   memberships m  ON m.user_id = u.id
JOIN   groups      g  ON g.id = m.group_id
JOIN   groupings   gi ON gi.group_id = g.id 
WHERE  gi.repo_id = 1
GROUP  BY u.id, g.permission
ORDER  BY u.id, g.permission DESC

The GROUP BY is of no use. DISTINCT ON alone gives you a unique users.id. Remove the GROUP BY, you'll get the same result, just faster.

You may want to add more columns to ORDER BY to decide which row to pick where more than one per id share the highest permission. The way you have it, you get an arbitrary pick for this case (provided it can occur).

My reference answer for this query technique has more explanation, a benchmark and links.

Hey! Thanks a lot for the link. It also includes info about how to add a proper index. <3 <3 <3 — Nando Vieira, Dec 16 '12 at 06:04

Remove row duplicates and get the highest value with PostgreSQL

1 Answers1