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:
| cnt | src_cd |
|---|---|
| 100 | CCC |
| 90 | BBB |
| 80 | AAA |
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
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