Sorting in Oracle Groupings (rank function)

Demand:

Find out how many top50 each vendor sells in each type of product

Analysis:

1. Query, GROUP by the specified field (vendor, product type), take the first 50 rows of each group, and view the number of each vendor

2. Use the Rank function to rank the sales of each vendor, each type of product, and add a fake ran

3. Filter the rows with the ran less than or equal to 50, group by vendor, product type, Count

Sql:

SelectGyscode Vendor code, typeid type ID,Count(1) Num from(SelectT.gyscode, T.typeid,sum(T.sale) sale, rank () Over (partition by T.gyscode, T.typeid ORDER by sum (B.sale) desc ) ran fromT_sale Twhere         --Calculation period: 1 to last monthT.log_date>=Add_months (Trunc (Sysdate,'mm'),-1)        andT.log_date<=Last_day (Add_months (Trunc (Sysdate,'mm'),-1))          Group  byT.gyscode, T.typeid)
Where ran <=Group  byGyscode, typeID

Note: There are two uses of the Runk function:

Dense_rank: Consecutive rankings, if there are two vendors of the same sales, ranked the same, but the next supplier ranked +1

Rank: non-sequential ranking, if there are two vendors of the same sales, ranked the same, but the next supplier ranked +2 (2: The number of vendors with the same sales)

The Runk function is ranked in two ways:

1. Rank all results (no partition, equivalent to rownum sorted by a field)

2. Group ranking (plus partition)

Sorting in Oracle Groupings (rank function)