CODEWITHSUNDEEP
sqlsql-serverconditional-statements
RK13
RK13

Reputation: 23

SQL select rows given they were included in TOP in a given year

I'm trying to select previous years' data for the top-selling products from this year to see how they've done over time.

I currently have something like: 

SELECT TOP 10 * 
WHERE Table.Year = 2016
ORDER BY Table.Transactions DESC

which gets me the data for the current year, but I'm not sure how to pull data for that same top 10 in previous years, given that this year's top 10 likely differs from previous years' top 10.

I was wondering if there was a way to do something along the lines of:

SELECT *  
WHERE Table.Year = 2015 
AND (order ID was in Top 10 in 2016) 
ORDER BY Table.Transactions DESC

Except clearly I have no idea what to put in place of the bracketed condition.

Any suggestions would be greatly appreciated. Thanks!

Upvotes: 2

Views: 147

Answers (4)

David דודו Markovitz
David דודו Markovitz

Reputation: 44971

select      *

from       (select      t.*
                       ,dense_rank () over (partition by t.year order by t.transactions desc)   as dr

            from        t
            ) t

where       t.dr <= 10

order by    t.year
           ,t.dr
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Use window functions:

select t.*
from (select t.*,
             row_number() over (partition by year order by transactions desc) as seqnum
      from t
     ) t
where seqnum <=  10;

Another fun way is to use cross apply:

select t.*
from (select 2015 as yyyy union all select 2016) y cross apply
     (select top 10 t.*
      from t
      where t.year = y.yyyy
      order by transactions desc
     ) t;

Instead of listing out the years explicitly, you can use a subquery (select distinct year from t) y.

Upvotes: 1

S3S
S3S

Reputation: 25142

Note ID should be some unique value like a PK

SELECT * 
FROM YourTable
WHERE YourTable.Year = 2015
AND ID IN
(SELECT TOP 10 ID 
WHERE Table.Year = 2016
ORDER BY Table.Transactions DESC)

Upvotes: 0

John Boker
John Boker

Reputation: 83729

do you want this:

SELECT *  
From Table
WHERE Table.Year = 2015 
AND ID IN (
    SELECT TOP 10 ID
    FROM Table
    WHERE Table.Year = 2016 
    ORDER BY Table.Transactions DESC
)
ORDER BY Table.Transactions DESC

Upvotes: 1

Related Questions