Search code examples
sqloraclewindow-functionstop-n

Rank function oracle sql

I am new to sql and working on assignment. There is a question, where I am supposed to use rank function, but I have no idea how to use that. I tried to find some information on the internet, but still having problems. I am posting question here, if you have any articles or comments which would be helpful, please post here.

Thanks,

Solution

  • When we use rank() ordered by descending POST_DATE the most recent date has the rank of 1:

    select aid
       , email
       , trunc((rnk-1)/5) + 1 as pageno
from ( select aid
              , email
              , rank() over (order by post_date desc ) as rnk
       from ads )

    You may need to adjust the calculation of page number; the question is somewhat ambiguous on that score ("the page number where the ad would be listed for all ads that would be listed in a page other than the first page ").

    As @AndriyM comments, in real life we would use ROW_NUMBER() for this rather than RANK() because that function would produce a distinct number for each row without gaps, which neither RANK() nor DENSE_RANK can guarantee. That would look like 

    row_number() over ( order by post_date desc, aid asc ) as rn

    Please bear in mind that your teacher is probably smart enough to know about StackOverflow, so be honest about assigning credit where it's due.