CODEWITHSUNDEEP
sqloracle-database
Alex Hoppus
Alex Hoppus

Reputation: 3935

SQL- query - select best car num

Suppose you have the following tables:

TABLE Car
car_id
num

TABLE Car_description
dsc_id
car_id
model

TABLE Result
res_id
car_id
race_num
result

the task is to select max, average scores for races where we have more then 2 cars and Car.num, Car_description.model for best cars. i wrote the following query

SELECT 
race_num,
MAX(result),
AVG(result),
STDDEV(result)
FROM Result r, Car c, Car_description d
WHERE c.car_id = r.car_id AND d.car_id = c.car_id
HAVING COUNT(result) > 2
GROUP BY r.race_num

but i don't understand - how to select best car num and model for each race? Thanks.

Upvotes: 2

Views: 543

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I am assuming that the car_id is a 1-1 mapping from car to car_description.

The following query uses row_number() to get the top car, based on the score. This information may already be present in "result".

select r.race_num,
       MAX(score) as MaxScore,
       AVG(score) as AvgScore,
       MAX(case when seqnum = 1 then c.carnum end) as TopCarNum,
       MAX(case when seqnum = 1 then cd.model end) as TopCarModel
from (select r.*,
             ROW_NUMBER() over (partition by race_num order by score desc) as seqnum
      from Result r
     ) r left outer join
     Car c
     on c.car_id = r.car_id left outer join
     Car_Description cd
     on c.car_id = d.car_id
group by r.race_num
having COUNT(*) > 2

The key to this is a group by, with conditionally choosing information about the top car.

Upvotes: 1

Related Questions