Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
saratchandran

New User

Joined: 24 Dec 2006
Posts: 3
Location: India

 Posted: Thu Jul 05, 2007 9:57 am    Post subject: How to use Rank Function in DB2 Version 8 Hi I have used this query in DB2 Ver 8 SELECT Col1,Col2, RANK() OVER(ORDER BY Col2 DESC) Cant We use Rank Function in DB2 Ver 8. Could any one tell me how to use Rank Function ?

dick scherrer

Site Director

Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

Posted: Mon Jul 09, 2007 6:16 am    Post subject:

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.

 Quote: 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: RANK 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.
saratchandran

New User

Joined: 24 Dec 2006
Posts: 3
Location: India

 Posted: Mon Jul 09, 2007 12:35 pm    Post subject: Re: How to use Rank Function in DB2 Version 8 Thank you..
dick scherrer

Site Director

Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

 Posted: Mon Jul 09, 2007 7:40 pm    Post subject: You're welcome
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm Spool Extract for Z /os 1.04 version Khadhar Basha CLIST & REXX 3 Thu Sep 28, 2017 3:14 pm What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am GDG version number PANDU1 JCL & VSAM 4 Sat Mar 04, 2017 8:28 pm RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us