一个SQL语句
表ta:
lotid       trxtype
1               a
1               b
1               b
1               c
1               c
1               c
2               a
2               b
2               b
2               c
2               c
想要得到的结果：
lotid       trxtype
1               c
2               b
或者
lotid       trxtype
1               c
2               c
即要得到每个lotid对应的记录最多的一个trxtype
请问这样的语句应该怎么写？

------解决方案--------------------
create table t2(lotid int, trxtype varchar2(100));
insert into t2
select 1, 'a ' from dual union all
select 1, 'b ' from dual union all
select 1, 'b ' from dual union all
select 1, 'c ' from dual union all
select 1, 'c ' from dual union all
select 1, 'c ' from dual union all
select 2, 'a ' from dual union all
select 2, 'b ' from dual union all
select 2, 'b ' from dual union all
select 2, 'c ' from dual union all
select 2, 'c ' from dual;
--执行查询
select lotid,trxtype from
(
select lotid,trxtype, row_number() over (partition by lotid
order by lotid,c desc) rn
from (select lotid,trxtype,count(*) c from t2 group by lotid,trxtype)
) where rn=1
--查询结果
1 c
2 b