RE: [sqr-users] RANK function

["Paul Hoyte" <pi_hoyte@si-proservices.com>]

Tam:

select empnum &empnum
Dept &dept
Salary &salary
rank() over (partition by dept order by salary desc nulls last) as rank
&rank1
dense_rank() over (partition by dept order by salary desc nulls last) as
denserank &rank2
row_number() over (partition by dept order by salary desc nulls last)as
rownumber &rank3
from emptab
 
Don't forget to use the "as fieldname" then the SQR &column_name

Paul Hoyte
(248) 752-6970
pa_hoyte@si-proservices.com

-----Original Message-----
From: sqr-users-bounces+pi_hoyte=si-proservices.com@sqrug.org
[mailto:sqr-users-bounces+pi_hoyte=si-proservices.com@sqrug.org] On Behalf
Of tam kag
Sent: Monday, January 08, 2007 5:52 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] RANK function

for some reason the line feed and carriage return was removed from the code,
I will try again
 
begin-select
--
--
--
store.company_number &company_numbersku8 numbersku8.sku_6_digit &sku6_digit
(,10)
sum(fst.current_send_store_qty) &transfered_qty (,20)
sum(fst.send_sku_retail_price_amt_lcy * fst.current_send_store_qty)
&transfered_price
rank() over (partition by store.company_number,sku8.sku_6_digit order by
&transfered_price) &temp (,40)
--
--
--
end-select



> From: kaghdot@hotmail.com> To: sqr-users@sqrug.org> Date: Mon, 8 Jan 2007
22:20:44 +0000> Subject: [sqr-users] RANK function> > Hello,> I'm in the
process of writing a top 10 report. I want to take advantage of the DB2
function RANK> Here is the relevant code in the begin-select block:>
begin-select...store.company_number &company_numbersku8.sku_6_digit
&sku6_digit (,10)sum(fst.current_send_store_qty) &transfered_qty
(,20)sum(fst.send_sku_retail_price_amt_lcy * fst.current_send_store_qty)
&transfered_price rank() over (partition by
store.company_number,sku8.sku_6_digit order by &transfered_price) &temp
(,40)...end-select> I know that putting a SQR column variable is not correct
but I want to rank by that metric. Also, just to test the rank function I
have replaced the column variable with fst.send_sku_retail_price_amt_lcy but
that did not work either.> Does any one know how to use the RANK function in
SQR? please let me know what I'm doing wrong here.> > Thank you>
_________________________________________________________________> Fixing up
the home? Live Search can help.>
http://imagine-windowslive.com/search/kits/default.aspx?kit=improve&locale=e
n-US&source=wlmemailtaglinenov06> >
_______________________________________________> sqr-users mailing list>
sqr-users@sqrug.org> http://www.sqrug.org/mailman/listinfo/sqr-users
_________________________________________________________________
Fixing up the home? Live Search can help.
http://imagine-windowslive.com/search/kits/default.aspx?kit=improve&locale=e
n-US&source=wlmemailtaglinenov06

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users