Nishant Roy Nishant Roy - 5 months ago 23
SQL Question

SELECT columns OVER (PARTITION BY column)

TABLE BEING QUERIED (tableA)

Suppose I want to retrieve the swimmer and their time at the 75th Percentile for each day.

This is what I was trying to do:

SELECT tableA.DATE, tableA.SWIMMER, tableA.TIME
OVER (PARTITION BY tableA.DATE)
FROM tableA
WHERE RANK = CEIL(0.75 * NUM_OF_SWIMMERS);


But this errors at the OVER statement.

What's the best way to get the data I need?

Thanks!

Answer

Your error is that the OVER clause of a windowing function requires an ORDER BY clause.

But assuming that num_swimmers , why not just return

select
   date,
swimmer,
time
from tablea
where 
RANK = CEIL(0.75 * NUM_OF_SWIMMERS)

?

The WHERE clause will ensure the only rows returned are the 75th percentile for a given day

Comments