Selecting the top n rows within a group by clause

sql, sql-server

Solution

CROSS APPLY is how you usually do this - http://msdn.microsoft.com/en-us/library/ms175156.aspx

EDIT - add example, something like this:

select
    bar1.instrument
    ,bar2.*
from (
    select distinct instrument from bar) as bar1
cross apply (
    select top 5
        bar2.instrument
        ,bar2.bar_dttm
        ,bar2.bar_open
        ,bar2.bar_close 
    from bar as bar2 where bar2.instrument = bar1.instrument) as bar2

Typically you would want to add an order by in there.

Edit - added distinct to the query, hopefully that gives you want you want. Edit - added missing 'select' keyword at top. copy & paste bug FTL!

Problem

I have schema similar to the following: ``` create table bar ( instrument varchar(255) not null, bar_dttm datetime not null, bar_open int not null, bar_close int not null ) ``` I would like to query the table, and return the most recent 5 rows per instrument. I can do it instrument by instrument, with: ``` select top 5 instrument, bar_dttm, bar_open, bar_close from bar where instrument = 'XXX' order by bar_dttm desc ``` I'd like to do this for all instruments at once in one query. Is this possible? I am running SQL Server 2008.

Original source