Find top n average values

I have a table that looks like this:

UID DATE GROUP VALUE

The data types are:

 VARCHAR DATE VARCHAR NUMERIC

Example:

abc1000 2012-09-01 1205 1000.0000
abc1000 2012-09-01 1210 1010.0000
abc1000 2012-09-02 1205 1100.0000
abc1000 2012-09-02 1210 1020.0000
def1010 2012-09-01 1205 2000.0000

I need to find the top N(15) values (VALUE) for each unique "UID+GROUP" and AVERAGE them so the output looks like this:

abc1000 1205 1050.0000
abc1000 1210 1015.0000
def1010 1205 2000.0000

Answers


select uid, [group], avg(value)
from
(
 select *,
        row_number() over (partition by uid, [group] order by value desc) rn 
        from yourtable
) v
where rn<=15
group by uid, [group]

Need Your Help

Remove rounded corners below UIStatusBarStyleBlackOpaque on iPhone running iOS 6

ios ios6 statusbar uistatusbar

Since iOS 6.0 the OS adds rounded corners below the statusbar with style UIStatusBarStyleBlackOpaque on the iPhone.

Message id issue connecting chat between iOS and Android in Quickblox

android ios quickblox

I'm having an issue connecting an iOS and Android device with their corresponding QBChat libraries, with the message ID's.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.