Eliminating similar row but with specific values

Question

I have user table and user_group table. A single user can be in multiple group. I have two groups admin and user. Admin group has group id 1 and user group has group id 2. As I select from joining these two table. I would like to select it using join between two table:

select * from users u
join user_groups ug
on u.user_id = ug.user_id;

I get Admin two times in each row. I would like to eliminate other row for admin with group id as user i.e 1 in this query how can I achieve this? The sqlfiddle is here. Group related information is in all_groups table.

Answer 1

Assuming that you want to keep the row with the largest group_id value

select *
  from (select u.user_id,
               u.username,
               u.first_name,
               u.middle_name,
               u.last_name,
               u.password,
               ug.group_id,
               rank() over (partition by ug.user_id
                                order by ug.group_id desc) rnk
          from users u
               join user_groups ug
                 on u.user_id = ug.user_id)
 where rnk = 1;

should work. Here is the sqlfiddle