Selecting the top n rows within a group by clause

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 bar1cross 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!

using SQL 2008, you could use a partitioned row number clause with a CTE...

with MyCte AS (SELECT      instrument,                            bar_dttm,                            bar_open,                            bar_close,                           PartitionedRowNum = ROW_NUMBER() OVER (PARTITION BY instrument ORDER BY bar_dttm DESC)               from        bar)select  *from    MyCtewhere   PartitionedRowNum <= 5

Row_Number can also be used - http://msdn.microsoft.com/en-us/library/ms186734.aspx

WITH foo as (Select * ,ROW_NUMBER() OVER(PARTITION BY instrument ORDER BY bar_dttm desc) as rankfrom bar)select  *from foowhere rank <= 5