RK13 RK13 - 1 month ago 4
SQL Question

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!

Answer

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 
Comments