SQL statement to get distinct data

Question

ID | user_id | name      | active
1  | 1       | Profile 1 | f
2  | 1       | Profile 2 | t
3  | 2       | Profile 3 | f
4  | 2       | Profile 4 | f
5  | 3       | Profile 5 | f

I'm using PostgreSQL. In my application,users can create multiple profiles and I want to select last distinct inactive profiles created by each user. Also, if there is an active profile belongs to that user, it should not select any profile from that user -- that was the hard part for me.

What kind of SQL statement I should use in order to get the following results?

4  | 2       | Profile 4 | f
5  | 3       | Profile 5 | f

Gordon Linoff · Answer 1 · 2014-02-08T13:29:24.467

2

The distinct on syntax works very well for this:

select distinct on (user_id) id, user_id, name, active
from t
where active = 'f'
order by user_id, id desc;

EDIT:

To avoid an active profile, it probably easier to go to analytic functions:

select id, user_id, name, active
from (select t.*,
             row_number() over (partition by user_id, active order by id desc) as seqnum,
             max(case when active = 'f' then 0 else 1 end) as numActives
      from t
     ) t
where numActives = 0 and seqnum = 1;

edited Feb 08 '14 at 13:29

answered Feb 07 '14 at 19:36

Gordon Linoff

1,242,037
58
646
786

It does, expect it also select rows with users who has an active profile. – Eren CAY Feb 07 '14 at 19:40
@ErenCAY . . . Yes, that makes it hard to use `distinct on`. See the edited version. – Gordon Linoff Feb 07 '14 at 19:49
@GordonLinoff It might be better to use `bool_or(active) as anyActive` instead of `max(case when active = 'f' then 1 else 0 end) as numActives` – Ihor Romanchenko Feb 07 '14 at 19:56
@GordonLinoff Also you do not need to `partition by user_id, active`. `partition by user_id` will be enough. – Ihor Romanchenko Feb 07 '14 at 19:58
@IgorRomanchenko . . . True about the first point, although I prefer to use more standard constructs when I can. As for the second one, I thought about that and it also works. – Gordon Linoff Feb 07 '14 at 20:00
1

@GordonLinoff It is OK to use more standart constructs, but still you save a mistake in `max(case when active = 'f' then 1 else 0 end) as numActives`. It will return `1` if any record has `active = 'f'`. You need `case when active then 1 else 0 end` – Ihor Romanchenko Feb 07 '14 at 20:04
@ErenCAY: The first query forgets about active profiles and the second one has the logic backwards. – Erwin Brandstetter Feb 07 '14 at 21:01
@ErwinBrandstetter You are right. Also, second one seems like hard to maintain. – Eren CAY Feb 08 '14 at 10:03

Clodoaldo Neto · Answer 2 · 2014-02-07T20:06:00.303

2

SQL Fiddle

select distinct on (user_id)
    id, user_id, name, active
from
    t
    inner join
    (
        select user_id
        from t
        group by user_id
        having not bool_or(active)
    ) s using(user_id)
order by user_id, id desc

edited Feb 07 '14 at 20:06

answered Feb 07 '14 at 19:57

Clodoaldo Neto

118,695
26
233
260

score 2 · Accepted Answer · edited May 23 '17 at 11:49

2

I would combine DISTINCT ON with NOT EXISTS. Assuming a proper boolean type for active:

SELECT DISTINCT ON (user_id)
       id, user_id, name, active
FROM   profiles p
WHERE  NOT EXISTS (
   SELECT 1 FROM profiles
   WHERE  user_id = p.user_id
   AND    active               -- exclude users with any active profiles
   )
ORDER  BY user_id, id DESC;

Probably fastest and cleanest.

edited May 23 '17 at 11:49

Community

1
1

answered Feb 07 '14 at 20:53

Erwin Brandstetter

605,456
145
1,078
1,228

SQL statement to get distinct data

3 Answers3