SQL Oracle return only max(value) of column value

Question

My data looks like this.

The output I am looking to see is:

Here is a query I wrote which is not quite there yet but I know the logic of what I should be doing. I need to take the max value of column(rate) of the same vehicle_size and competitor. This could be written in a better way so I would appreciate it if someone can point me to the right direction.

Below is my query:

SELECT RENTAL_DATE, OUTBOUND, INBOUND CASE WHEN Competitor = 'kay' AND VEHICLE_SIZE= 'Small' THEN MAX(RATE) WHEN COMPETITOR = 'lola' AND VEHICLE SIZE = 'Small'THEN MAX(RATE) WHEN Competitor = 'kay' AND VEHICLE_SIZE= 'Large' THEN MAX(RATE) WHEN COMPETITOR = 'lola' AND VEHICLE SIZE = 'Large'THEN MAX(RATE) ELSE 'RATE' END AS RATE FROM FORMATTED2018AND2019DATA;

Second query:

select Rental_date, outbound, inbound, vehicle_size, max(rate) where competitor='lola' and vehicle_size = 'small' OR 'large' max(Rate)where competitor ='kay' and vehicle_size = 'small' OR 'large' from table2 group by Rental_date, outbound, inbound, vehicle_size,

You said I need to take the max value of column(rate) of the same vehicle_size and competitor. What about other columns value do you want in your output? — Pham X. Bach
– Pham X. Bach, Commented Feb 28, 2019 at 3:08
Then if you want all only max value of column rate for each group of all others column, you could simply use aggregate function as in @fa06's answer — Pham X. Bach
– Pham X. Bach, Commented Feb 28, 2019 at 3:16

Fahmi · Accepted Answer · 2019-02-28 03:08:16Z

2

Try below -

select  Rental_date, competitor,outbound, inbound, vehicle_size, 
        max(rate) 
        from table2 
        group by 
        Rental_date, competitor,outbound, inbound, vehicle_size

answered Feb 28, 2019 at 3:08

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Oracle return only max(value) of column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related