we can dynamically number rows in a SELECT Transact-SQL statement
this may be the only possible solution and which is faster than the procedural solution. Row numbering or ranking is a typical procedural issue. The solutions are typically based on loops and temporary tables; therefore, they are based on SQL Server loops and cursors. This technique is based on an auto join. The chosen relationship is typically "is greater than." Count how many times each element of a particular set of data fulfills the relationship "is greater than" when the set is compared to itself.
The following example is based on the pubs database.
In this example:
1.Set 1 is authors.
2.Set 2 is authors.
3.The relationship is "last and first names are greater than."
4.You can avoid the duplicate problem by comparing the first + last names to the other first + last names.
5.Count the number of times the relationship is fulfilled by count(*).
select rank=count(*), a1.au_lname, a1.au_fname from authors a1, authors a2 where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname group by a1.au_lname, a1.au_fname order by rank
Result:
Rank Au_Lname Au_Fname
---- -------------- -----------
1 Bennet Abraham
2 Blotchet-Halls Reginald
3 Carson Cheryl
4 DeFrance Michel
5 del Castillo Innes
6 Dull Ann
7 Greene Morningstar
8 Green Marjorie
9 Gringlesby Burt
10 Hunter Sheryl
11 Karsen Livia
12 Locksley Charlene
13 MacFeather Stearns
14 McBadden Heather
15 O'Leary Michael
16 Panteley Sylvia
17 Ringer Albert
18 Ringer Anne
19 Smith Meander
20 Straight Dean
21 Stringer Dirk
22 White Johnson
23 Yokomoto Akiko
(23 row(s) affected)
