distinct 和 order by 的 矛盾

select distinct cont_id from cms_contentiteminfo t order by cast (t.click as int) desc

上面这句报错ORA-1791,not a SELECTED expression

select distinct cont_id ,cast (t.click as int) as cfrom cms_contentiteminfo t order by cast (t.click as int) desc

查出的结果是有重复记录的

cont_id c

1853371517
1856391003
185586985
185337967
185641937
185337899
185639832
185641832
185641727
185283649
185144605
185586588
185639564
185216547
185586508
185283492
185144422
185283421
185216378
185217369
185144346
185626309
185217303
192278254

修改为

select distinct cont_id,max(cast (t.click as int)) c from cms_contentiteminfo t group by cont_id order by c desc

查询结果

cont_id c

1853371517
1856391003
185586985
185641937
185283649
185144605
185216547
185217369
185626309
192278254
185145234
191809214
185286191
190484190
185642190
190487180
185287171
190486162
192483146
189151138
191253118
177607117
192704116
185140115

cont-id重复的记录就可以去掉了