Вопросы Теги

Выбор вершины n строки в группе пунктом

Если аудит включен на сервере, просто используйте 

SELECT *
FROM ALL_TAB_MODIFICATIONS
WHERE TABLE_NAME IN ()
10
задан Jon 16 June 2009 в 21:37
поделиться

3 ответа

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!

12
ответ дан 3 December 2019 в 18:00
поделиться

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
3
ответ дан 3 December 2019 в 18:00
поделиться

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
7
ответ дан 3 December 2019 в 18:00
поделиться
Другие вопросы по тегам:

Похожие вопросы: