No SQL Server 2000, sempre que precisamos numerar os registros resultantes de um SELECT de forma dinâmica, as sugestôes normalmente são baseadas em loops e tabelas temporárias. Para a nossa felicidade, o SQL Server 2005 vem com uma função chamada ROW_NUMBER() a qual nos permite realizar esta operação de forma bem mais simples. No exemplo abaixo, podemos ver como esta função pode ser utilizada.
USE AdventureWorks
GO
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS ‘Row Number’,
c.FirstName, c.LastName,a.PostalCode
FROM Sales.SalesPerson s
JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
Row Number FirstName LastName PostalCode
———- ———– ———– ———-
1 Shelley Dyck 98027
2 Gail Erickson 98055
3 Maciej Dusza 98027
4 Linda Ecoffey 98027
5 Mark Erickson 98055
6 Terry Eminhizer 98055
7 Michael Emanuel 98055
8 Jauna Elson 98055
9 Carol Elliott 98027
10 Janeth Esteves 98055
11 Martha Espinoza 98055
12 Carla Eldridge 98027
13 Twanna Evans 98055
A dica desta semana mostra como podemos obter este resultado no SQL Server 2000 sem precisar usar loops ou tabelas temporárias. A técnica é baseada em JOINS, o qual inclusive tende a ser bem mais performática que as soluções tradicionais.
No primeiro exemplo, utilizamos um INNER JOIN da tabela pubs..authors com ela mesma e contamos o número de vezes que o relacionamento “é maior igual que” é satisfeito, agrupando por lastname e firstname.
SELECT rank=count(*), a1.au_lname, a1.au_fname
FROM authors a1 INNER JOIN authors a2
ON a1.au_lname >= a2.au_lname
GROUP BY a1.au_lname, a1.au_fname
ORDER BY 1
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 Green Marjorie
.
.
.
(23 row(s) affected)
No segundo exemplo, utilizamos tabelas derivadas para obter as lojas com seus respectivos números de livros vendidos. O relacionamento é baseados no número de livros vendidos “é maior igual que”.
SELECT rank=count(*), s1.stor_id, qty=sum(s1.qty)
FROM (SELECT stor_id, qty=sum(qty) FROM sales GROUP BY stor_id) s1,
(SELECT stor_id, qty=sum(qty) FROM sales GROUP BY stor_id) s2
WHERE s1.qty >= s2.qty
GROUP BY s1.stor_id
ORDER BY 1
rank stor_id qty
——- ——- —-
1 6380 8
2 7896 120
3 8042 240
4 7067 360
5 7066 625
6 7131 780
(6 row(s) affected)
Mais informações e exemplos sobr a utilização dessa técnica podem ser vistos no artigo KB 186133: How to dynamically number rows in a SELECT Transact-SQL statement