Get the Top 10 of each distinct column value / The Lazy Developer

select Pub.Code, Pub.Name, WeekdayName = DateName(weekday,Cast(Cast(EditionDate as nvarchar) as datetime)), EditionDate 
from
(
   SELECT *,
         --adds a row number
         ROW_NUMBER() OVER (PARTITION BY lnkpublicationID ORDER BY EditionDate DESC) AS rn
   FROM src_PublicationEdition
   where EditionDate between 20140101 and 20180101
) a
Inner Join src_Publication PUB on PUB.IDPublication = a.lnkPublicationID
--we can then limit by row number
where rn <= 10


--- OR

;with MAIN As
(
select Pub.Code, Pub.Name, WeekdayName = DateName(weekday,Cast(Cast(EditionDate as nvarchar) as datetime)), EditionDate, rn 
from
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY lnkpublicationID ORDER BY EditionDate DESC) AS rn
   FROM src_PublicationEdition
   where EditionDate between 20140101 and 20180101
) a
Inner Join src_Publication PUB on PUB.IDPublication = a.lnkPublicationID
)
select * from MAIN
where rn < 10
order by Code

select Pub.Code, Pub.Name, WeekdayName = DateName(weekday,Cast(Cast(EditionDate as nvarchar) as datetime)), EditionDate

from

(

SELECT *,

--adds a row number

ROW_NUMBER() OVER (PARTITION BY lnkpublicationID ORDER BY EditionDate DESC) AS rn

FROM src_PublicationEdition

where EditionDate between 20140101 and 20180101

) a

Inner Join src_Publication PUB on PUB.IDPublication = a.lnkPublicationID

--we can then limit by row number

where rn <= 10

--- OR

;with MAIN As

(

select Pub.Code, Pub.Name, WeekdayName = DateName(weekday,Cast(Cast(EditionDate as nvarchar) as datetime)), EditionDate, rn

from

(

SELECT *,

ROW_NUMBER() OVER (PARTITION BY lnkpublicationID ORDER BY EditionDate DESC) AS rn

FROM src_PublicationEdition

where EditionDate between 20140101 and 20180101

) a

Inner Join src_Publication PUB on PUB.IDPublication = a.lnkPublicationID

)

select * from MAIN

where rn < 10

order by Code