SQL Server Performance Forum – Threads Archive
udf assistance
Hi there, Not being an expert in using sql server or the udf functionality, I would like to see if I am able to get assistance with a problem that I have. I am using sql server 2000 to start with. Now the problem that i have is that I would like to get a ranking based on an account number. For example. MAPPINGDATE CODECURRDESC POSTACC_NONULL15.01.20074000USDJanuary Movements50300720
NULL15.01.20074000USDJanuary Movements50300720
NULL15.01.20074000USDJanuary Movements40300720
NULL15.01.20074000USDJanuary Movements40322000
NULL15.01.20074000USDJanuary Movements50322060
NULL15.01.20074000USDJanuary Movements50323100
NULL15.01.20074000USDJanuary Movements50323100
NULL15.01.20074000USDJanuary Movements50325050
NULL15.01.20074000USDJanuary Movements50326040
NULL15.01.20074000USDJanuary Movements50326040
NULL15.01.20074000USDJanuary Movements40326040
NULL15.01.20074000USDJanuary Movements50326060
NULL15.01.20074000USDJanuary Movements50326060
NULL15.01.20074000USDJanuary Movements50326060
NULL15.01.20074000USDJanuary Movements50326070
NULL15.01.20074000USDJanuary Movements50326070
NULL15.01.20074000USDJanuary Movements50326070
NULL15.01.20074000USDJanuary Movements50327010
NULL15.01.20074000USDJanuary Movements50327010
is to end up with a ranking so to speak with the column MAPPING MAPPINGDATE CODECURRDESC POSTACC_NO
115.01.20074000USDJanuary Movements50300720
115.01.20074000USDJanuary Movements50300720
115.01.20074000USDJanuary Movements40300720
215.01.20074000USDJanuary Movements40322000
315.01.20074000USDJanuary Movements50322060
415.01.20074000USDJanuary Movements50323100
415.01.20074000USDJanuary Movements50323100
515.01.20074000USDJanuary Movements50325050
615.01.20074000USDJanuary Movements50326040
615.01.20074000USDJanuary Movements50326040
615.01.20074000USDJanuary Movements40326040
715.01.20074000USDJanuary Movements50326060
715.01.20074000USDJanuary Movements50326060
715.01.20074000USDJanuary Movements50326060
815.01.20074000USDJanuary Movements50326070
815.01.20074000USDJanuary Movements50326070
815.01.20074000USDJanuary Movements50326070
915.01.20074000USDJanuary Movements50327010
915.01.20074000USDJanuary Movements50327010 So, what I am trying to achieve is, whenever the ACC_NO is the same, assign a MAPPING number to it starting from 1 and increasing everytime the ACC_NO changes. Is there anyone who is able to assist with this little dilemna. Thanks
Ged
check if this helps…http://sqlservercode.blogspot.com/2006/03/ranking-in-sql-server-2000.html ***********************
Dinakar Nethi
SQL Server MVP
***********************
Thanks Dinakar. I have had a quick look at this one earlier, but will definately take a stronger look at it and see if it does meet my needs.
Ged
With SQL 2000 and earlier, you have to create the rank list in a temp table, like this: CREATE TABLE #Rank (MAPPING INT IDENTITY(1,1), ACC_NO VARCHAR(100)) INSERT INTO #Rank (ACC_NO)
SELECT DISTINCT ACC_NO
FROM mytable
ORDER BY ACC_NO … then join the temp table to your source table: SELECT r.MAPPING, t.*
FROM mytable t INNER JOIN #Rank r ON t.ACC_NO = r.ACC_NO
ORDER BY r.Mapping
Thankyou all. Your advice was invaluable.
You can also try this. If your table is large, this might be slow.
declare @table table
(
MAPPING int,
[DATE] datetime,
CODE int,
CURR varchar(10),
[DESC] varchar(20),
POST varchar(10),
ACC_NOint
)
set dateformat dmy
insert into @table
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 300720union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 300720union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 40, 300720union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 40, 322000union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 322060union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 323100union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 323100union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 325050union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 326040union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 326040union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 40, 326040union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 326060union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 326060union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 326060union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 326070union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 326070union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 326070union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 327010union all
selectNULL, ‘15.01.2007’, 4000, ‘USD’, ‘January Movements’, 50, 327010 updatet
setMAPPING = (select count(distinct ACC_NO) from @table x where x.ACC_NO <= t.ACC_NO)
[email protected] t select* from @table KH
]]>