Summary of the Oracle rank () function

first of all, to understand rank in English means: rank. That is, it is a function that determines the level of the data.

Take sales as an example, there are regions, years, months, salespeople, sales, records of these five fields. We can sort salespeople by region, year, month, sales, which is the equivalent of a level concept for salespeople, the first one is the highest selling ... if we're going to find each region, year, month, The top three sales members of the sale. How does SQL write?

Java code

SELECT Area_code, year, month, Saleroom,saler          RANK () up     (PARTITION by Area_code,year, month ORDER by Area_code, Year,month,saleroom) RANK from   T_sale  

Now rank is 1,2,3,3,3,6, with this field, it's easy to get the top three salespeople.

New question: Sales of 50000 yuan in Shenzhen, May 2007 can be ranked to the first?

SQL code

1. SELECT          rank (' SHENZHEN ', 2007,5,50000)  within GROUP          (ORDER by area_code,year,month,saleroom) rank    the above SQL will be done. Note that the argument in Rank () must be a constant, or constant expression, the number of arguments inside, and the type of the field that corresponds to the order by.

The above is the two usage of the rank function. There is also a dense_rank (), which is used just like rank (), and only calculates the hierarchy differently. For example, the above

1,2,3,3,3,6. With Dense_rank () is 1,2,3,3,3,4.

Summary of the Oracle rank () function