August 20, 2009 at 3:19 pm
I have a table that records search results and is in this format
row_id IDENTITY PK, (records are inserted in order that they are shown)
pageview_id int (identifies the search term)
adv_id int
What Im trying to to is there will be many row_ids from one search. I need to number the search to show how they ranked.
I need
row_id, pageview_id, adv_id rank
23454 45324 23 1
23455 45324 56 2
23456 45324 84 3
23457 45325 56 1
23458 45325 120 2
August 20, 2009 at 3:40 pm
First of all, you don't need a cursor for this. Secondly, what determines the order? Is it the Adv_ID value or the IDENTITY column?
This should get you started:
DECLARE @Rank TABLE(row_id int, pageview_id int, adv_id int)
INSERT INTO @Rank
SELECT 23454, 45324, 23 UNION ALL
SELECT 23455, 45324, 56 UNION ALL
SELECT 23456, 45324, 84 UNION ALL
SELECT 23457, 45325, 56 UNION ALL
SELECT 23458, 45325, 120
SELECT PageView_ID,
Adv_ID,
ROW_NUMBER() OVER(PARTITION BY PageView_ID ORDER BY Adv_ID) as [Rank]
FROM @Rank
GROUP BY PageView_ID, Adv_ID
August 21, 2009 at 8:18 am
The order is the identity. Thanks.
August 21, 2009 at 10:17 am
DECLARE @Rank TABLE(row_id int, pageview_id int, adv_id int)
INSERT INTO @Rank
SELECT 23454, 45324, 23 UNION ALL
SELECT 23455, 45324, 56 UNION ALL
SELECT 23456, 45324, 84 UNION ALL
SELECT 23457, 45325, 56 UNION ALL
SELECT 23458, 45325, 120
SELECT r1.Row_ID,
r1.PageView_ID,
r1.Adv_ID,
r2.[Rank]
FROM @Rank r1
INNER JOIN (
SELECT Row_ID,
PageView_ID,
ROW_NUMBER() OVER(PARTITION BY PageView_ID ORDER BY Row_ID) as [Rank]
FROM @Rank
GROUP BY PageView_ID, Row_ID
) r2 ON r1.Row_ID = r2.Row_ID
August 21, 2009 at 10:39 am
In addition to ROW_NUMBER(), you should also read up on the RANK() and NTILE() functions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply