Oracle去重取最新数据列 row_number() over(partition by 去重列 order by 排序列 desc）

 查询

with tmp_t as
 (select '1' as userid, 'A' as workgroups
    from dual
  union
  select '2' as userid, 'A' as workgroups
    from dual
  union
  select '3' as userid, 'B' as workgroups
    from dual
  union
  select '3' as userid, 'C' as workgroups from dual)
select t.*,
       count(1) over(partition by t.workgroups) cnt,
       row_number() over(partition by t.workgroups order by t.userid desc) rn
  from tmp_t t

cnt是 workgroups出现总数，rn是根据userid排序取最新workgroups相同的数据

结果

userid	workgroups	cnt	rn
1	A	2	1
2	A	2	2
3	B	1	1
4	C	1	1