A Data grouping report was created several days ago and then re-ordered. It is written in crystal script. First, set a global variable to auto-add it, And then clear it in each group head. The code is very simple and I will not post it.
After a short time, a colleague sought a grouping and then re-ordered the statement. He thought that my report was made using a similar SQL statement. In fact, if Oracle is used, the rank function can also implement grouping and re-sorting.
Rank Function
The rank function produces an ordered ranking of rows starting with a rank of one. Users specify an optional
Partition clause and a required order by clause. The partition keyword is used to define where the rank
Resets. The specific column which is ranked is determined by the order by clause. If no partition is specified,
Ranking is saved med over the entire result set. Rank will assign a rank of 1 to the smallest value unless descending
Order is used. The following example ranks salesmen for each region based on their sales amount.
Select sales_person, sales_region, sales_amount,
Rank () over (partition by s_region order by s_amount DESC)
From sales_table;
Sales_person, sales_region, sales_amount, rank
Adams East 100 1
Baker East 99 2
Connors East 89 3
Davis East 75 4
Edwards West 74 1
Fitzhugh West 66 2
Gariabaldi West 45 3