Los que hemos trabajado con las distintas versiones de SLQ Server a lo largo de los años (desde la versión 6.5, pasando por la 7 ,2000, 2005 y ahora 2008) hemos visto la buena evolución de este producto, y a decir verdad, no deja de sorprenderme gratamente.
He aquí que cuando hemos tenido la necesidad de añadir un número secuencial al ejecutar una consulta, todos nos las hemos ingeniado con las posibilidades que nos da T-SQL. Quien no ha utilizado alguna vez los alias de tablas, para extraer información fila por fila de datos relacionados con la misma tabla.
En el siguiente ejemplo, vamos a añadir una primera columna que nos va a indicar el número de fila que le corresponde. Utilizaremos para ello la tabla Authors de la base de datos Pubs (sigo añorando la utilización de ésta para pruebas y aprendizaje):
Tabla Author |
Select (Select COUNT(au_lname) from authors b where a. au_id>=b.au_id ) as secuencia, au_lname,au_fname, city ,au_id from authors a |
secuencia |
au_lname |
au_fname |
city |
au_id |
1 |
White |
Johnson |
Menlo Park |
172-32-1176 |
2 |
Green |
Marjorie |
Oakland |
213-46-8915 |
3 |
Carson |
Cheryl |
Berkeley |
238-95-7766 |
4 |
O’Leary |
Michael |
San Jose |
267-41-2394 |
5 |
Straight |
Dean |
Oakland |
274-80-9391 |
6 |
Smith |
Meander |
Lawrence |
341-22-1782 |
7 |
Bennet |
Abraham |
Berkeley |
409-56-7008 |
8 |
Dull |
Ann |
Palo Alto |
427-17-2319 |
9 |
Gringlesby |
Burt |
Covelo |
472-27-2349 |
10 |
Locksley |
Charlene |
San Francisco |
486-29-1786 |
11 |
Greene |
Morningstar |
Nashville |
527-72-3246 |
12 |
Blotchet-Halls |
Reginald |
Corvallis |
648-92-1872 |
13 |
Yokomoto |
Akiko |
Walnut Creek |
672-71-3249 |
Como vemos, hemos conseguido extraer un número secuencial para la tabla Authors. Quizás que para mí una forma útil, pero un tanto rígida, porque nos hemos tenido que basar en el orden de la clave primaria (au_id).
En las últimas versiones de SqlServer (estoy hablando de la 2005 y 2008) ha aparecido una función llamada ROW_NUMBER, que devuelve el número secuencial de una fila de un conjunto de resultados, comenzando con 1. En el siguiente ejemplo lo mostraré:
Tabla Author con Row_number ordenado por apellido y nombre |
Select ROW_NUMBER() OVER(ORDER BY au_lname,au_fname ) as secuencia,au_lname,au_fname, city ,au_id from authors |
secuencia |
au_lname |
au_fname |
city |
au_id |
1 |
Bennet |
Abraham |
Berkeley |
409-56-7008 |
2 |
Blotchet-Halls |
Reginald |
Corvallis |
648-92-1872 |
3 |
Carson |
Cheryl |
Berkeley |
238-95-7766 |
4 |
DeFrance |
Michel |
Gary |
722-51-5454 |
5 |
del Castillo |
Innes |
Ann Arbor |
712-45-1867 |
6 |
Dull |
Ann |
Palo Alto |
427-17-2319 |
7 |
Green |
Marjorie |
Oakland |
213-46-8915 |
8 |
Greene |
Morningstar |
Nashville |
527-72-3246 |
9 |
Gringlesby |
Burt |
Covelo |
472-27-2349 |
10 |
Hunter |
Sheryl |
Palo Alto |
846-92-7186 |
11 |
Karsen |
Livia |
Oakland |
756-30-7391 |
12 |
Locksley |
Charlene |
San Francisco |
486-29-1786 |
Pero donde verdaderamente me fascina esta función es a la hora de utilizarla para filtrar datos. Puede parecer simple, pero resulta muy útil en ocasiones en las que la lógica de la consulta se complica. Vamos a utilizar un sencillo ejemplo.
Vanos a extraer las filas con los 5 mejores valores de la columna Quantity (Qty), durante el año 1993.
Tabla Sales filtrando 5 valores a elegir (Qty) |
WITH Quantity AS
SELECT * FROM Quantity |
RowQty |
stor_id |
ord_num |
ord_date |
qty |
payterms |
title_id |
1 |
7066 |
A2976 |
1993-05-24 00:00:00.000 |
50 |
Net 30 |
PC8888 |
2 |
7896 |
X999 |
1993-02-21 00:00:00.000 |
35 |
ON invoice |
BU2075 |
3 |
8042 |
QA879.1 |
1993-05-22 00:00:00.000 |
30 |
Net 30 |
PC1035 |
4 |
8042 |
P723 |
1993-03-11 00:00:00.000 |
25 |
Net 30 |
BU1111 |
5 |
7131 |
P3087a |
1993-05-29 00:00:00.000 |
25 |
Net 60 |
PS7777 |
Sé que existen otras maneras de hacerlo, pero al entender este ejemplo veremos su potencial que seguramente nos sacará de un buen apuro.
Aprovecho para mandarte un fuerte abrazo, querido lector en esta mi primera publicación en este blog
Un saludo,
Javier Egea
Hola Javier,
bienvenido a Geeks.
Muy interesante tu entrada sobre ROW_NUMBER().
Felicidades por la entrada. 😉
Hola Javier, interesante tú artículo. Llevo tiempo peleándome con T-SQL y no sabía la existencia de está función, gracias.
Por otro lado quería darte la enhorabuena por tu primer artículo y desearte que le sigan muchas más.
Un saludo.
Hola Javier
Bienvenido a Geeks y felicidades por tu primer artículo. Espero que nos sigas contando cosas interesantes 😎
Ciertamente, desde que en SQL Server 2005 contamos con ROW_NUMBER(), se pueden conseguir estupendos resultados, que con SQL Server 2000 requerían soluciones más enrevesadas ;-).
Un saludo
Luismi
Have a DYNAMITE day my friend!
Comme un débutant, je suis toujours à la recherche en ligne pour les articles qui peuvent m’aider. Merci Wow! Merci! J’ai toujours voulu écrire quelque chose dans mon site comme ça. Puis-je prendre une partie de votre post sur mon blog?
We’re a group of volunteers and starting a brand new initiative in a community. Your weblog supplied us valuable information to work on. You have done a marvellous work!