CODEWITHSUNDEEP
sqlhana
Nishant Roy
Nishant Roy

Reputation: 1141

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!

Upvotes: 0

Views: 685

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

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

Upvotes: 2

Related Questions