搜索distinct表但返回所有列

Question

我需要查询一个包含3行的表: id、ip和user_id

我只想返回具有唯一值ip和user_id的行(本身不是唯一的，而是成对唯一的)，例如：

id         ip          user_id
--------------------------------
1          1           2
2          1           2
3          1           2
4          2           5
5          2           5
6          2           8
7          2           8
8          3           10
9          3           11

结果必须是：

id        ip
------------
1         1
4         2
2         8
6         2
8         3
9         3

我使用的是Oracle数据库。

Answer 1

select id, ip
from the_table
  join (
     select min(id) as min_id
     from the_table
     group by ip, user_id
  ) t on t.min_id = the_table.id
order by id;

或者：

select id, ip
from (
   select id, 
          ip,
          row_number() over (partition by ip_user_id order by id) as rn
   from the_table
) t 
where rn = 1
order by id;

Answer 2

select min(id),ip from table
group by ip,user_id