CODEWITHSUNDEEP
sqlsql-servermaxgreatest-n-per-groupwindow-functions
Katharine Toll
Katharine Toll

Reputation: 11

Find second-largest value within bounded window in SQL Server

SQL question - Does anyone know of a way to select the second-highest value from a bounded window? I have pricing data for a set of geographies/products/weeks. I need to create a new column that, for each row, shows the second-highest price offered for a given geo/product over the past 13 weeks. I basically need to find an NTH_ROW () equivalent in ADW. Ideally, the solution would look similar to the following but identify the 2nd-highest price rather than the max (unless there was only one week of pricing registered during the 13 wks, in which case it would populate that price):


    comparison_price = MAX(price) over (
                          PARTITION BY geography, product
                          ORDER BY week
                          ROWS BETWEEN 12 PRECEDING AND CURRENT ROW

This image shows an example of what I would want the comparison_price column to populate for each week of a specific product/geo:

Example Table

I tried using an NROW() workaround, but received an error saying that the window needs to be unbounded. I've also considered creating a separate CTE but am trying to find a more elegant solution. Any thoughts?

Thanks!

Upvotes: 1

Views: 696

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

A lateral join is the way to go. I would phrase it as:

select t.*, t2.price as comparison_price
from t outer apply
     (select distinct t2.price
      from (select top (13) t2.*
            from t t2
            where t2.geography = t.geography and
                  t2.product = t.product
                  t2.week <= t.week
            order by t2.week desc
           ) t2
       order by t2.price desc
       offset 1 row fetch first 1 row only
      ) t2;

Upvotes: 2

GMB
GMB

Reputation: 222482

I think the simplest approach might be a lateral join, and window functions:

select t.*, coalesce(x.price, t.price) as comparison_price
from mytable t
outer apply (
    select *
    from (
        select t1.*,
            row_number() over(partition by geography, product order by price desc) rn
        from mytable t1
        where t1.geography = t.geography and t1.product = t.product and t1.week <= t.week
        order by t1.week
        offset 0 rows fetch first 13 rows only
    ) x
    where x.rn = 2
) x

Upvotes: 2

Related Questions