CODEWITHSUNDEEP
sql-server
user3129097
user3129097

Reputation: 181

How to use top n with over() clause of SQL Server

The following article (https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql) claims:

One can find out "top N per group" results using over clause.

Please give me an example.

Yours sincerely

Upvotes: 1

Views: 556

Answers (2)

Mustafa Erdogan
Mustafa Erdogan

Reputation: 113

Its an easy task

MAX(column) OVER(PARTITION BY column) AS mx

You will get the max value for each column value, which is TOP N

Replace MAX with other aggregate function to get another insight, like MIN, AVG etc.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

One method is:

select t.*
from (select t.*,
             row_number() over (partition by grp order by col) as seqnum
      from t
     ) t
where seqnum <= @n;

Upvotes: 2

Related Questions