November 1, 2011 at 6:00 am
I've come across some code that fills a temp table in 3 parts
CREATE TABLE #Tmp
( pKey INT,
SortType varchar(50),
StatusType INT );
INSERT INTO #Tmp
SELECT Sort1, 1
FROM Table
WHERE Status <= 5;
INSERT INTO #Tmp
SELECT Sort2, 2
FROM Table
WHERE Status >= 6 AND Status <= 10;
INSERT INTO #Tmp
SELECT Sort3, 3
WHERE Status > 10;
It then goes on to Sort the temp table by StatusType, SortType.
I was wondering if I could achieve the same result using a RANK function.
Something like (pseudo-code)
;WITH RankedData AS (
SELECT pKey,
RANK() OVER (PARTITION BY ..Status_Range..) AS StatusType
Sort1,
Sort2,
Sort3
FROM Table
)
INSERT INTO #tmp
SELECT pKey,
CASE StatusType
WHEN 1 THEN Sort1
WHEN 2 THEN Sort2
WHEN 3 THEN Sort3
END,
StatusType
FROM RankedData;
But how do I get the Status_Range into the Partition by clause.
November 1, 2011 at 6:37 am
Doh! (too quick to post before engaging brain)
I've solved my immediate problem without using RANK at all
; WITH RankedData AS (
SELECT pKey,
Sort1,
Sort2,
Sort3,
CASE
WHEN OrderStatus <= 5 THEN 1
WHEN OrderStatus >= 6 AND OrderStatus <= 10 THEN 2
WHEN OrderStatus > 10 THEN 3
END AS StatusType
FROM Table )
INSERT INTO #Tmp
SELECT pKey
CASE
WHEN STatusType = 1 THEN Sort1
WHEN StatusType = 2 THEN Sort2
WHEN StatusType = 3 THEN Sort3
END,
StatusType
FROM RankedData;
This works fine for my purposes and gives me a 25% performance boost.
However if anyone can work out how to use Ranking functions over a range then I'd be interested to hear your ideas.
November 1, 2011 at 7:06 am
You would just use your CASE expression for your PARTITION BY expression.
SELECT Rank() Over( PARTITION BY CASE WHEN ... THEN ... END ORDER BY expression )
FROM YourTable
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply