Numerar los registros de un Select

Un truco para numerar los registros retornados por un comando SELECT-SQL.

Este truco fue tomado del artículo "Una consulta interesante escrita para SQL Server 2000 y SQL Server 2005" del amigo Miguel Egea (SQL Server MVP y Webmaster de PortalSQL).

Muchas veces he leído en diversos mensajes en el Foro de VFP, que se necesita numerar los registros retornados por un SELECT para pintar los registros alternados de un control Grid, o también para enumerar los documentos por clientes, o los ítems por factura. Con este truco, esto se puede lograr en una sola sentencia SELECT.

La idea es hacer un JOIN contra la misma tabla y contar los registros que tengan el identificador mayor o igual que la segunda tabla del JOIN.

Para hacer mas gráfica la idea, tomemos el ejemplo de enumerar los Clientes de la tabla Customers de la base de datos Northwind:

OPEN DATABASE (HOME(2) + "Northwind\Northwind")
SELECT COUNT(*) AS Registro, c1.CustomerId, c1.CompanyName ;
  FROM Customers C1 ;
  INNER JOIN Customers C2 ON C1.CustomerId >= C2.CustomerId ;
  GROUP BY C1.CustomerId, C1.CompanyName ;
  ORDER BY Registro

Con esta mismo concepto, también podemos hacer "cortes" en la numeración y reinicializarla según una condición.

En este otro ejemplo numeramos las Ordenes de la tabla Orders y hacemos un corte por Cliente:

OPEN DATABASE (HOME(2) + "Northwind\Northwind")
SELECT COUNT(*) AS Registro, O1.OrderId, O1.CustomerId, O1.ShipName ;
  FROM Orders O1 ;
  INNER JOIN Orders O2 ON O1.CustomerId = O2.CustomerId ;
  AND O1.OrderId >= O2.OrderId ;
  GROUP BY O1.OrderId, O1.CustomerId, O1.ShipName ;
  ORDER BY O1.CustomerId, Registro

A partir de Visual FoxPro 9.0, las mejoras incorporadas en el comando SELECT-SQL nos permite retornar el mismo conjunto de datos utilizando subconsultas.

Los mismos 2 ejemplos anteriores con sintaxis para VFP9:

OPEN DATABASE (HOME(2) + "Northwind\Northwind")
SELECT (SELECT COUNT(*) ;
  FROM Customers C2 ;
  WHERE C1.CustomerId >= C2.CustomerId) AS Registro, C1.* ;
  FROM Customers C1 ;
  ORDER BY Registro

OPEN DATABASE (HOME(2) + "Northwind\Northwind")
SELECT (SELECT COUNT(*) ;
  FROM Orders O2 ;
  WHERE O1.OrderId >= O2.OrderId AND ;
  O1.CustomerId = O2.CustomerId) AS Registro, O1.* ;
  FROM Orders O1 ;
  ORDER BY O1.CustomerId, Registro

Nota: No utilizar este truco con tablas que contengan muchos registros, ya que la performance del JOIN contra la misma tabla puede ser muy pobre. Antes de implementar esta solución, es conveniente comprobar que la velocidad de respuesta no afecte considerablemente los tiempos de nuestra aplicación.

Hasta la próxima,

Luis María Guayán