Top 100 sum with row number

Question

I have select:

select col1,col2,col3.....sum(colx) from t1 group by col1,col2,col3...

How to:

1- display top 100 rows with highest sum(colx1) ordered ASC

2- add row numbers

Answer 1

use TOP() and ROW_NUMBER() :

SELECT TOP 100 t.*,
       ROW_NUMBER() OVER(ORDER BY sum_col DESC) as row_number 
FROM(
    select col1,col2,col3.....
           sum(colx) as sum_col
    from t1 
    group by col1,col2,col3...) t
ORDER BY sum_col