Search This Blog

Tuesday, August 19, 2008

Ranking rows of a result set...

Rows can be ranked in a result set [Selection from one table or multiple tables by using joins] by using Rank() function. If two or more rows tie for a rank, tied rows will get the same rank.

Example:
Two temporary tables created, and populated with test data, two queries created one for single table selection and second query to show the results from two tables by using joins.

CREATE TABLE #TEMP
(ID INT
,VAL1 VARCHAR(1)
,VAL2 VARCHAR(10)
,VAL3 VARCHAR(5)

)

CREATE TABLE #TEMP1
(ID1 INT
,VAL11 VARCHAR(1)
,VAL21 VARCHAR(10)
,VAL31 VARCHAR(10)

)


INSERT INTO #TEMP VALUES(1,'A','ABC','TEST1')
INSERT INTO #TEMP VALUES(1,'F','ADC','TEST2')
INSERT INTO #TEMP VALUES(1,'G','ABD','TEST3')
INSERT INTO #TEMP VALUES(2,'H','ABC','TEST4')
INSERT INTO #TEMP VALUES(2,'K','ADC','TEST5')
INSERT INTO #TEMP VALUES(3,'L','ABD','TEST6')

INSERT INTO #TEMP1 VALUES(1,'Z','ABC','TESTING1')
INSERT INTO #TEMP1 VALUES(2,'R','ABC','TESTING4')
INSERT INTO #TEMP1 VALUES(3,'P','ABD','TESTING6')

Single table selection:
SELECT *,RANK() OVER(PARTITION BY ID ORDER BY VAL1) RANK
FROM #TEMP
ID val1 val2 val3 RANK
1 A ABC Test1 1
1 F ADC Test2 2
1 G ABD Test3 3
2 H ABC Test4 1
2 K ADC Test5 2
3 L ABD Test6 1

Multiple tables selection:
SELECT *,RANK() OVER(PARTITION BY VAL21 ORDER BY VAL1) RANK
FROM #TEMP T
INNER JOIN #TEMP1 T1 ON T.ID=T1.ID1

ID val1 val2 val3 ID1 val11 val21 val31 RANK
1 A ABC Test1 1 Z ABC Testing1 1
1 F ADC Test2 1 Z ABC Testing1 2
1 G ABD Test3 1 Z ABC Testing1 3
2 H ABC Test4 2 R ABC Testing4 4
2 K ADC Test5 2 R ABC Testing4 5
3 L ABD Test6 3 P ABD Testing6 1

No comments: