Dense rank substitute in SQL Server 2000

Question

Dense rank substitute in SQL Server 2000

SQLPain

Hall of Fame

Points: 3522
More actions
October 6, 2015 at 9:02 am

#308598

Hi,
One of our servers is running on SQL Server 2000, I have the same database in 2008 for which I wrote a CTE as follows:
Since SQL Server 2000 does not support these ranking functions, what else I can do? Self join wont be possible as there are over 130 Reason codes.
With Applications_CTE
AS
(
SELECT
AP.AppId
,R.ReasonType
,R.ReasonCode
,R.ReasonText
,dense_rank() OVER (PARTITION BY AP.AppID ORDER BY R.ReasonCode) AS [Dense Rank]
Result set:
AppId.........ReasonType...........ReasonCode.....ReasonText...................................Dense Rank
415020............D......................4...............Excessive Debt Relative to Income........1
415020............D......................9...............Delinquency Date Too Recent...............2
415020............D.....................17..............Excessive Payment Relative to Income3
415020...........D.....................118............Insufficient Installment Credit..............4

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You could use the Quirky Update to get the desired result. If you don't want to mess with it, another option would be to use a well written cursor (not using the defaults).

To understand how the quirky update works and the rules to follow, you should read this article: http://www.sqlservercentral.com/articles/T-SQL/68467/

In the end, the solution would look something like this:

CREATE TABLE #SampleData(

AppIdint,

ReasonTypechar(1),

ReasonCodeint,

ReasonTextvarchar(100),

DenseRank int, --This is to compare the results

CalcDenseRank int);

CREATE CLUSTERED INDEX CI_Sample ON #SampleData( AppId, ReasonCode)

INSERT INTO #SampleData(

AppId,

ReasonType,

ReasonCode,

ReasonText,

DenseRank

) --Change this select to insert the data needed.

SELECT 415020,'D',4, 'Excessive Debt Relative to Income', 1 UNION ALL

SELECT 415020,'D',17, 'Excessive Payment Relative to Income', 3 UNION ALL

SELECT 415020,'D',9, 'Delinquency Date Too Recent ', 2 UNION ALL

SELECT 415020,'D',17, 'Excessive Payment Relative to Income', 3 UNION ALL

SELECT 415020,'D',4, 'Excessive Debt Relative to Income', 1 UNION ALL

SELECT 415020,'D',118, 'Insufficient Installment Credit ', 4 UNION ALL

SELECT 4150,'D',4, 'Excessive Debt Relative to Income', 1 UNION ALL

SELECT 4150,'D',17, 'Excessive Payment Relative to Income', 3 UNION ALL

SELECT 4150,'D',9, 'Delinquency Date Too Recent ', 2 UNION ALL

SELECT 4150,'D',17, 'Excessive Payment Relative to Income', 3 UNION ALL

SELECT 4150,'D',4, 'Excessive Debt Relative to Income', 1 UNION ALL

SELECT 4150,'D',118, 'Insufficient Installment Credit ', 4 ;

DECLARE @Rank int = 0,

@AppId int = 0,

@ReasonCodeint = 0

UPDATE t WITH(TABLOCKX) SET

@Rank = CalcDenseRank = CASE WHEN AppId = @AppId AND ReasonCode = @ReasonCode

THEN @Rank

WHEN AppId = @AppId AND ReasonCode <> @ReasonCode

THEN @Rank + 1

ELSE 1 END,

@AppId = AppId,

@ReasonCode = ReasonCode

FROM #SampleData t

OPTION( MAXDOP 1);

SELECT *

FROM #SampleData;

GO

DROP TABLE #SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

Thank you Luis, I will try this tonight and let you know if it works.