19

How can I add ROW numbers to this query result?

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mario
  • 13,941
  • 20
  • 54
  • 110

2 Answers2

20

Add: ROW_NUMBER() OVER (ORDER BY NumCars)

EDIT:

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
Tomek
  • 3,267
  • 2
  • 22
  • 23
  • Tomek I don't understand where in the query I have to put that? – Mario Feb 05 '12 at 17:02
  • I have tried your query but I get this error: Msg 208, Level 16, State 1, Line 1 Invalid object name 'VehicleSpecs'. I also have tried with [CarSpecs].[dbo].[VehicleSpecs] ...but I get the same error... – Mario Feb 05 '12 at 17:41
  • Very strange the error appears only in MSSQL studio and in my asp.net application works. – Mario Feb 05 '12 at 17:43
  • I need to order by NumCars DESC and if I add DESC then the row numbers are displayed in reverse orders, they should be displayed 1,2,3 ... – Mario Feb 05 '12 at 17:49
  • I have solved the problem, Thanks ! I had to add DESC to both ORDER BY – Mario Feb 05 '12 at 17:52
  • @MarioM: have you selected correct database in the SSMS before running the query? You can also put this statement before the query: USE yourDbName; – Tomek Feb 05 '12 at 18:17
20

Wrap you entire query in a sub query and add row_number in the outer query.

select *, row_number() over(order by (select 0)) as rn
from
  (
    select distinct -- your columns
    from YourTable
  ) as T
order by NumCars desc
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Does MS SQL not allow an empty `over()` clause? –  Feb 05 '12 at 15:45
  • @a_horse_with_no_name - Not for row_number. It does for max and min. – Mikael Eriksson Feb 05 '12 at 15:47
  • 2
    @MikaelEriksson: thanks. Wouldn't `order by 0` just do the same thing? –  Feb 05 '12 at 15:48
  • @MartinSmith - Yes I thought that could be important at first but since he is doing a group by as well I don't think it would matter in this case. – Mikael Eriksson Feb 05 '12 at 15:49
  • @a_horse_with_no_name - It interprets any integer constant as an attempt to use a column ordinal and that is disallowed. constants aren't permitted in general though either. `ORDER BY 1.0/0` works though as this is not integer datatype and doesn't get folded into a constant. – Martin Smith Feb 05 '12 at 15:49
  • @a_horse_with_no_name - That would make sense but it gives an error "Windowed functions do not support constants as ORDER BY clause expressions." – Mikael Eriksson Feb 05 '12 at 15:50
  • Mikael I have tried as you said, but I get this error: Msg 1033, Level 15, State 1, Line 10 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. – Mario Feb 05 '12 at 17:05
  • @MarioM Ah, yes you need to move your order by statement to the outer query. `ORDER BY NumCars` – Mikael Eriksson Feb 05 '12 at 17:24