Naturalmente, solo números, con ROW_NUMBER en T_SQL













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 ROW_NUMBER() OVER (ORDER BY qty desc) AS ‘RowQty’, stor_id, ord_num, ord_date, qty, payterms, title_id
FROM sales
where YEAR(ord_date) = 1993)

 

SELECT * FROM Quantity
WHERE RowQty BETWEEN 1 AND 5;

 

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

 

6 comentarios en “Naturalmente, solo números, con ROW_NUMBER en T_SQL”

  1. 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.

  2. 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

  3. 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?

  4. 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!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *