oracle - Avoid redundant code in SQL query -

oracle - Avoid redundant code in SQL query -

i have table lite athletic's competitions results. players earns points first 3 places. need list names of athletics points. have this:

select name, sum(many) sum ( (select name, count(*) * (select points "points place" place = 1) many results r1 (select count(*) results r2 r1.result < r2.result , r1.discipline = r2.discipline , r1.city = r2.city) = 0 grouping name) union (select name, count(*) * (select points "points place" place = 2) many results r1 (select count(*) results r2 r1.result < r2.result , r1.discipline = r2.discipline , r1.city = r2.city) = 1 grouping name) union (select name, count(*) * (select points "points place" place = 3) many results r1 (select count(*) results r2 r1.result < r2.result , r1.discipline = r2.discipline , r1.city = r2.city) = 2 grouping name) ) grouping name order sum;

i have 3 times same redundant code. if weren't depend place, utilize view...

you can simplify with:

select r.name, sum(p.points) total_points (select city, discipline, name, row_number() on (partition city, discipline order result) place results r) r inner bring together "points place" p on p.place = r.place p.place <= 3 grouping r.name order total_points desc;

* note: if there can tie, , want both count place, utilize dense_rank() instead of row_number()

also where p.place <= 3 may redundant given test info have 3 places scored...so can omit that. i've left in though.

but i'm confused in original sql , erwins answer, you've both inverted places. i.e guy ran longest 1st?!

i.e in fiddle had:

-- results -- insert results values ('9.87', 'doha', '100m', 'justin gatlin'); ... insert results values ('10.28', 'doha', '100m', 'jimmy vicaut');

now read justin won in 9.87 seconds. yet you've both counted jimmy's win.

if so, analytic should be

partition city, discipline order result desc

sql oracle aggregate-functions