CODEWITHSUNDEEP
sqlgreatest-n-per-group
a3.14_Infinity
a3.14_Infinity

Reputation: 5843

How to optimize a SQL Query containing a max?

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)

Upvotes: 0

Views: 76

Answers (3)

MrZed
MrZed

Reputation: 85

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions