row_number - numero de fila sql server

Agregar número de fila a esta consulta T-SQL

Question

¿Cómo puedo agregar números de ROW a este resultado de la consulta?

SELECT DISTINCT
VehicleSpecs.SubmittedById,
COUNT(VehicleSpecs.SubmittedById) AS NumCars,
aspnet_Users.UserName
FROM
VehicleSpecs
INNER JOIN aspnet_Users ON VehicleSpecs.SubmittedById = aspnet_Users.UserId
WHERE
(LEN(VehicleSpecs.SubmittedById) > 0)
GROUP BY
VehicleSpecs.SubmittedById,
aspnet_Users.UserName
ORDER BY
NumCars DESC

Answer 1

Agregar: ROW_NUMBER() OVER (ORDER BY NumCars)

EDITAR:

WITH t1 AS
( SELECT DISTINCT
VehicleSpecs.SubmittedById ,
COUNT(VehicleSpecs.SubmittedById) AS NumCars ,
aspnet_Users.UserName
FROM VehicleSpecs
INNER JOIN aspnet_Users ON VehicleSpecs.SubmittedById = aspnet_Users.UserId
WHERE ( LEN(VehicleSpecs.SubmittedById) > 0 )
GROUP BY VehicleSpecs.SubmittedById ,
aspnet_Users.UserName
)
SELECT ROW_NUMBER() OVER ( ORDER BY NumCars ), *
FROM t1
ORDER BY NumCars

Answer 2

Envuelva su consulta completa en una sub consulta y agregue row_number en la consulta externa.

select *, row_number() over(order by (select 0)) as rn
from
(
select distinct -- your columns
from YourTable
) as T
order by NumCars desc