Please note that both T and T1 refer to same table. We are trying to retrieve a maximum value and while retrieving max value, we are interested in those rows, which have equal columnC values.
select *
from table T
where T.columnA in (0,1,2,3)
and T.columnB = (select max(T1.columnB)
from table T1
where T1.columnC = T.columnC)
This type of query is typically more efficient using window functions:
select *
from (
select *,
max(columnb) over (partition by columnc) as max_b
from the_table
where columna in (0,1,2,3)
) t
where columnb = max_b;
If the condition on columna is very selective an index on that column would help. Some optimizers might generate more efficient plans if you change columna in (0,1,2,3) into columna between 0 and 3
a_horse_sith_no_name is correct that window functions are generally a better approach. Regardless of window functions or your query, indexes will help.
In particular, you want indexes on T(columnc, columnb) and T(columnA). That is two separate indexes. The SQL optimizer should be able to take advantage of the indexes both for your query and for the window functions approach.
Not sure about where do you want (which layer) the columnA filter, but maybe like this:
Select tt1.* from table tt1
inner join
(
select * from
table t1
inner join
( select max(T0.columnB) max_columnB
from table t0 ) t2
on t1.columnB = t2.max_columnB
) tt2
on tt1.columnC = tt2.columnC
Where tt1.columnA in (0,1,2,3)
An index is needed for columnA, and columnB and for columnC to run fast.
