Archives of the TeradataForum

Message Posted: Mon, 09 Oct 2000 @ 14:46:14 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Barbara,

It has been a while since I have used it, but I think what it means by the 'sort_expression_list' is the combination of values you wish to rank and the order the values should be judged (I hope that makes sense!)

e.g. RANK (SALES_VALUE DESC, COST_VALUE ASC) would rank the most profitable transactions first, but it wouldn't be all that useful in this state, because 1) their is no descriptive values selected with it and 2) you would have an awful lot of numbers returned.

If you wish to rank the most profitable department you would have to use a derived table, also if you wished to restrict the returned answer set you need to make use of the QUALIFY statement.

     e.g.
     SELECT
             DEPT_NAME, RANK ( SALES_VALUE DESC, COST_VALUE ASC ) FROM
             (SELECT DEPT_NAME, SUM(SALES_VALUE), SUM(COST_VALUE)
              FROM
                     SALES
              GROUP BY 1
              )
     QUALIFY RANK ( SALES_VALUE DESC, COST_VALUE ASC) <= 10

Hope this helps

Regards

Lee

	<Prev	Next>		<<First	<Prev	Next>	Last>>