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

Yes It may affects

Second query is an example of Inline View. It's a very useful method for performing reports with various types of counts or use of any aggregate functions with it.

Oracle executes the subquery and then uses the resulting rows as a view in the FROM clause.

As we consider about performance , always recommend inline view instead of choosing another subquery type.

And one more thing second query will give all max records,while first one will give you only one max record.

see here