Re: [SPAM 1]sql rank() problem

[David FOXWELL <David.FOXWELL@xxxxxxxxx>]

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