Oracle-使用partition按条件去重注意事项

使用partition的条件需是文本或数字，日期格式会出现无法去重，要实现本年同一家医院取最新时间

日期格式下没有转换，对于两个月都出现了该家医院没有去重

select * from(
select RIQI,product,terminal_name,if_need_df,
ROW_NUMBER() over(partition by substr(RIQI,1,4),trim(product),trim(terminal_name) order by RIQI desc) top
 from o_not_df )where top=1 and terminal_name='大药房'

 

将日期格式转为文本后恢复正常

select * from(
select to_char(RIQI,'yyyymmdd'),product,terminal_name,if_need_df,
ROW_NUMBER() over(partition by substr(to_char(RIQI,'yyyymmdd'),1,4),trim(product),trim(terminal_name) order by to_char(RIQI,'yyyymmdd') desc) top
 from o_not_df )where top=1 and terminal_name='大药房'