CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2rankingrow-number
4est
4est

Reputation: 3168

Top 100 sum with row number

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

enter image description here

Upvotes: 0

Views: 1038

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use ROW_NUMBER and TOP:

SELECT TOP(100)
    col1,
    col2,
    col3,
    SUM(colx),
    Rn = ROW_NUMBER() OVER(ORDER BY SUM(colx) DESC)
FROM t1
GROUP BY
    col1,
    col2,
    col3
ORDER BY Rn

Upvotes: 1

sagi
sagi

Reputation: 40491

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

Upvotes: 2

Related Questions