Comparison Group by VS Over Partition By

Question

Assuming one table CAR with two columns CAR_ID (int) and VERSION (int).

I want to retrieve the maximum version of each car.

So there are two solutions (at least) :

select car_id, max(version) as max_version 
  from car  
 group by car_id;

Or :

select car_id, max_version 
  from  ( select car_id, version
               , max(version) over (partition by car_id) as max_version
            from car
                ) max_ver  
 where max_ver.version = max_ver.max_version

Are these two queries similarly performant?

Answer 1

I know this is extremely old but thought it should be pointed out.

select car_id, max_version 
  from (select car_id
             , version
             , max(version) over (partition by car_id) as max_version
          from car ) max_ver  
 where max_ver.version = max_ver.max_version

Not sure why you did option two like that... in this case the sub select should be theoretically slower because your selecting from the same table 2x and then joining the results back to itself.

Just remove version from your inline view and they are the same thing.

select car_id, max(version) over (partition by car_id) as max_version
  from car

The performance really depends on the optimizer in this situation, but yes the as original answer suggests inline views as they do narrow results. Though this is not a good example being its the same table with no filters in the selections given.

Partitioning is also helpful when you are selecting a lot of columns but need different aggregations that fit the result set. Otherwise you are forced to group by every other column.