tags:

views:

422

answers:

3

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.

+8  A: 

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!

This doesn't seem to work for me. I get many duplicate rows back as I think the apply is executing on each row in the bar table?
Jon
@jon - oops, didn't have any test data handy so I couldn't validate the query I wrote. Probably the simplest is to do a distinct on bar1.instrument in a subquery. I'll update the example.
@hainstech works fine now, but you're missing a select at the top.
Jon
Although this and the CTE example both work, this answer is much faster on my data so I have voted both up but accepted this one.
Jon
+5  A: 

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    MyCte
where   PartitionedRowNum <= 5
Scott Ivey
This works nicely. One problem I have is that my bar table is quite large (millions of rows) and the query plan for this seems to sort the entire table. Is there a way to optimize this? The top '5' rows represents a small percentage of the table (<1%).
Jon
+2  A: 

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 rank
from
 bar
)

select 
 *
from
 foo
where
 rank <= 5
jms