× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.