Understanding the relation between ranking functions, OVER(), GROUP BY?

Question

My book says that -

1- Ranking functions (such as RANK(), DENSE_RANK(), ROW_NUMBER() etc.) need an OVER() clause.

So, the code below is wrong -

select *, RANK()
from [grant]
order by Amount desc

error - Incorrect syntax near 'RANK', expected 'OVER'.

If I add OVER() after RANK() in above code, i get an error again. (Error - The ranking function "RANK" must have an ORDER BY clause.)

2- Then, my books adds that "Ranking functions need ORDER BY information to appear as an argument in the OVER()".

select *, RANK() OVER(ORDER BY Amount DESC) as GrantRank
from [grant]

My questions are -

1 - Why do we need to have an OVER() clause with a ranking function ? 2 - Why do we have to remove the order by statement and put it inside the OVER() ?

The book does not explain the logic behind these things ! Please help me to understand it.

Thanks in advance.

Answer 1

Why do we need to have an OVER() clause with a ranking function?

The OVER() clause is needed so that SQL Server knows exactly how you want to determine things like RANK(). What RANK() do you expect if you don't supply SQL Server with an ordering criteria? Is the winner of a race the one with the fastest time, the slowest time, or the first name alphabetically?

Why do we have to remove the order by statement and put it inside the OVER()?

You don't need to remove the ORDER BY clause when you add an ORDER BY clause inside the OVER(). These are used independently - one to determine the RANK() and the other to dictate ordering.

So, for example, if you wanted to return the finishers of a race, but order them last place to first place, you might say:

SELECT 
  name, 
  finish_time, 
  [rank] = RANK() OVER (ORDER BY finish_time) -- fastest first
FROM 
  dbo.race_table
ORDER BY 
  finish_time DESC; -- fastest last