Re: [SPAM 1]sql rank() problem

Thanks.

But the update doesn't work. I think I must generate a temporary table first and join to that. I'm afraid its going to look very complicated and I'll be obliged to write a program as I was asked to do in the first place.








* Subject: Re: [SPAM 1]sql rank() problem
* From: Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>

Close... you are going to need digits() and substr() or right() to limit it to 6 characters as the result of rank is 20 digits.

...concat right(digits( ...rank... ,6))...


On 05/18/2010 06:26 AM, David FOXWELL wrote:


I hope I have this right, as I am new to this kind of request :

My request :
SELECT 'ADH_' concat char( Rank() OVER ( order by Fld1, Fld2 )), Fld1, Fld2, Fld3
FROM mytable

Result :
CONCAT FLD1 FLD2 FLD3
ADH_1 biggs ron 10000
ADH_2 blogs fred 12000
ADH_2 blogs fred 11300
ADH_3 smith al 00002


I need the number part of the result of the concat to have 6 digits, how would I do that?
CONCAT FLD1 FLD2 FLD3
ADH_000001 biggs ron 10000
ADH_000002 blogs fred 12000
ADH_000002 blogs fred 11300
ADH_000003 smith al 00002
....
ADH_012345 xxxxx xx 01234

Finally, I will want to update a new field FLD4 with the result of the concat. Is that as easy as I think? Update mytable set fld4 = 'ADH_' concat char( Rank() OVER ( order by Fld1, Fld2 ))?

Thanks