subconsultas - sql select @@ rowcount

SQL: Numeración de las filas devueltas por una instrucción SELECT

Question

Supongamos que tengo una instrucción SELECT que devuelve un conjunto de resultados. ¿Hay alguna forma en que puedo enumerar mis resultados de la siguiente manera?

SELECCIONAR ARRIBA 3 Nombre FROM PuppyNames ORDER BY NumberOfVotes

me daría ...

Fido

Vagabundo

Freddy Krueger

...pero yo quiero...

1, Fido

2, Rover

3, Freddy Krueger

donde, por supuesto, las comas significan que los números están en su propia columna. [Estoy usando SQL Server 2000.]

Answer 1

Con SQL 2000 necesita usar una subconsulta correlacionada.

   SELECT (
              SELECT COUNT(*)
                FROM PuppyNames b
               WHERE b.Popularity <= a.Popularity
          ) AS Ranking
        , a.Name
     FROM PuppyNames a
 ORDER BY a.Popularity

Answer 2

Por lo general, es más fácil agregar los números en la aplicación del cliente. Hay trucos en SQL, pero para un purista implican hacer trampa, y generalmente no son portátiles.

Para mí, es uno de mis patrones de refactorización más básicos.

Answer 3

Puede utilizar esta consulta, que tiene en cuenta el PK para proporcionar una numeración correcta en caso de que el mismo NumberOfVotes:

SELECT TOP 3 COUNT(*) AS Number, p1.Name
FROM PuppyNames AS p1 INNER JOIN PuppyNames AS p2 
    ON p1.NumberOfVotes < p2.NumberOfVotes OR (p1.NumberOfVotes = p2.NumberOfVotes AND p1.ID >= p2.ID)
GROUP BY p1.Name
ORDER BY Number

Answer 4

SQL 2005, 2008:

SELECCIONE TOP 3 ROW_NUMBER () OVER (ORDER BY NumberOfVotes DESC) COMO VoteRank, Name FROM PuppyNames

EDITAR: Perdón, acabo de ver que especificó 2000.

Answer 5

También podrías hacerlo con una tabla temporal:

SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes DESC

se convierte

CREATE TABLE #RowNumberTable (
    RowNumber int IDENTITY (1,1),
    PuppyName varchar(MAX)
)
INSERT #RowNumberTable (PuppyName)
SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes DESC
SELECT * from #RowNumberTable ORDER BY RowNumber
DROP TABLE #RowNumberTable

Si lo notará, su declaración SELECT está ahí. Está rodeado de cosas que hacen que las filas funcionen.

Answer 6

En Microsoft SQL Server 2005, tiene la función ROW_NUMBER() que hace exactamente lo que desea.

Si está atascado con SQL Server 2000, la técnica típica era crear una nueva tabla temporal para contener el resultado de su consulta, además de agregar una columna de IDENTITY y generar valores incrementales. Vea un artículo que habla sobre esta técnica aquí: http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm