I am trying to pick the src_cd which has the maximum count in my inner query:

select count(*), src_cd 
from innertable1 
group by src_cd

Result for the above query is:

From the above result I want to do a

select * 
from table1 
where src_cd having max(cnt of src_cd from innertable1)

I also want to use row_number() to pick the 2nd max, 3rd max and so on

CodePudding user response：

You can use limit 1 with order by to pick largest.

select count(*), src_cd 
from innertable1 
group by src_cd
order by 1 desc
limit 1

Order by will order in descending order of count. limit will pick up first row.

You can also use subquery to calculate next max rows using row_number().

select src_cd as second_max
from (
select src_cd, row_number() over( order by cnt desc) as rownum
from (
select count(*) cnt, src_cd 
from innertable1 
group by src_cd
)rs
) rs2
where rownum=2 -- second MAX