Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
Hello and welcome to the forums,
Why did you choose that particular syntax? What you have specified is an OLAP function, typically used in a data warehouse/cube environment.
If you are not working in an OLAP situation, my guess is that you will not use RANK or DENSERANK.
Following is a bit of info from IBM and you may want to search deeper on IBM web pages.
the RANK function, which is an OLAP function provided by DB2 Universal Database? (DB2? UDB).
DB2 Cube Views supports the following OLAP functions that are provided by DB2 UDB:
Orders the rows and assigns a ranking to each row. The rank is defined as 1 plus the number of preceding rows that are distinct with respect to the ordering. If the relative order of two or more rows cannot be determined because they have duplicate row values, the same rank number is assigned. The ranking results might have gaps in the numbers if there are duplicate row values. Table 1 shows an example of what the ranking results are from the RANK function for a set of sample row values.
The typical syntax for the RANK function is:
RANK ( ) OVER (ORDER BY sort-key-expression expression-order)
where sort-key-expression is the set of data to be ranked, and expression-order is a keyword, ASC or DESC, that orders the values of the sort-key-expression in ascending or descending order. DB2 Cube Views requires that the sort-key-expression be an existing measure, not a column or attribute. Also, DB2 Cube Views does not support the PARTITION BY clause that is provided by DB2 UDB with this function. More information about the RANK function is available in the DB2 UDB Information Center.