Sql-server – Top 10 each year

sql servertop

I need to write a query to show the number of tracks sold per artist per year for the top 10 best selling artists. Take the overall top 10 for all years.

This is what I have:

SELECT  TOP 10 a.Name, YEAR(i.InvoiceDate), count(t.TrackId)
FROM Artist a 
INNER JOIN Album al on a.ArtistId = al.ArtistId
INNER JOIN Track t on al.AlbumId = t.AlbumId
INNER JOIN InvoiceLine il on t.TrackId = il.TrackId
INNER JOIN invoice i on  il.InvoiceId = i.InvoiceId
Group by YEAR(i.InvoiceDate), a.Name
ORDER by  count(t.TrackId) DESC;

enter image description here

I don't only the top 10 of all the tracks sold by on group and not by each year the top 10.

Best Answer

You could use the RANK() windowing function to accomplish this

SELECT rs.Name,rs.YearInvoice , rs.Counter
FROM (
    SELECT a.Name, YEAR(i.InvoiceDate) as YearInvoice, Rank() 
      over (Partition BY YEAR(i.InvoiceDate)
            ORDER BY count(t.TrackId) DESC ) AS Rank,count(t.TrackId) as Counter
      FROM Artist a 
INNER JOIN Album al on a.ArtistId = al.ArtistId
INNER JOIN Track t on al.AlbumId = t.AlbumId
INNER JOIN InvoiceLine il on t.TrackId = il.TrackId
INNER JOIN invoice i on  il.InvoiceId = i.InvoiceId
GROUP BY a.Name,YEAR(i.InvoiceDate)
    ) rs WHERE Rank <= 10
ORDER BY YearInvoice asc, Counter Desc;

Adapted from this answer

Reproducable with this dataset

I like Lord Of The Rings, don't judge

CREATE TABLE Artist (ArtistID int identity(1,1) NOT NULL Primary Key, Name varchar(255));
CREATE TABLE Album (Albumid int identity(1,1) NOT NULL Primary Key,ArtistID int, Albumname varchar(255));
CREATE TABLE Track (TrackId int identity(1,1) NOT NULL Primary Key, AlbumID Int, Trackname varchar(255));
CREATE TABLE InvoiceLine (InvoicLineeId int identity(1,1) NOT NULL Primary Key,TrackID int,InvoiceId int);
CREATE TABLE invoice (InvoiceId int identity(1,1) NOT NULL Primary Key,InvoiceDate datetime2);


INSERT INTO Artist(Name)
VALUES('Frodo'),('Gandalf'),('Bilbo'),('Aragorn'),('Legolas'),('Samwise'),('Smeagol'),('Gimli'),('Boromir'),('Smaug'),('Galadriel'),('Elrond') -- 12

INSERT INTO Album(ArtistID,Albumname)
VALUES(1,'Three rings'),(2,'For the Elven kings'), (3,'Seven for the Dwarf-lords'),(4,'in their halls of stone'),(5,'Nine for Mortal Men'),(6,'doomed to die')
,(7,'One for the Dark Lord'),(8,'on his dark throne'),(9,'In the Land of Mordor where the Shadows lie.'),(10,'One Ring to rule them all')
,(11,'One Ring to find them'),(12,'One Ring to bring them all and in the darkness bind them');

INSERT INTO Track(AlbumID,Trackname)
    VALUES(1,'Three rings'),(1,'Three rings'),(2,'For the Elven kings'),(2,'For the Elven kings'),(2,'For the Elven kings'),(3,'Seven for the Dwarf-lords'),(3,'Seven for the Dwarf-lords'),(3,'Seven for the Dwarf-lords'),(3,'Seven for the Dwarf-lords'), (3,'Seven for the Dwarf-lords'),(4,'in their halls of stone'),(5,'Nine for Mortal Men'),(6,'doomed to die')
,(7,'One for the Dark Lord'),(8,'on his dark throne'),(9,'In the Land of Mordor where the Shadows lie.'),(10,'One Ring to rule them all')
,(11,'One Ring to find them'),(12,'One Ring to bring them all and in the darkness bind them'),(1,'Three rings'),(1,'Three rings'),(1,'Three rings');


INSERT INTO InvoiceLine(TrackID,InvoiceId) VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17),(18,18),(19,19),(20,20),(21,21),(22,22);
INSERT INTO Invoice(InvoiceDate) 
VALUES(dateadd(year,-1,getdate())),
(dateadd(year,-1,getdate())),(dateadd(year,-1,getdate())),
(dateadd(year,-2,getdate())),(dateadd(year,-2,getdate())),
(dateadd(year,-2,getdate())),(dateadd(year,-3,getdate())),
(dateadd(year,-3,getdate())),
(dateadd(year,-3,getdate())),(dateadd(year,-4,getdate())),
(dateadd(year,-4,getdate())),(dateadd(year,-4,getdate())),
    (dateadd(year,-5,getdate())),(dateadd(year,-5,getdate())),
(dateadd(year,-6,getdate())),
(dateadd(year,-6,getdate())),(dateadd(year,-6,getdate())),
(dateadd(year,-6,getdate())),(dateadd(year,-7,getdate())),(dateadd(year,-1,getdate())),(dateadd(year,-1,getdate())),(dateadd(year,-1,getdate()));

Result

For each year, ordering by Counter desc, my dataset is not the best to get a clean result

Name      YearInvoice   Counter
Bilbo     2012  1
Bilbo     2013  3
Gandalf   2013  1
Frodo     2014  1
Gandalf   2014  1
Elrond    2015  1
Galadriel 2015  1
Smaug     2015  1
Boromir   2016  1
Gimli     2016  1
Smeagol   2016  1
Aragorn   2017  1
Legolas   2017  1
Samwise   2017  1
Frodo     2018  4
Gandalf   2018  1
Bilbo     2018  1
Related Question