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