Eirikur Eiriksson (7/31/2014)

---

Do you mean something like this?

😎

select * from (

select

[MediaName]

,[Spend]

,[ClientName]

,[BookingYear]

, ROW_NUMBER() OVER(ORDER BY [Spend] DESC) As Row

from(

select

lhe.LHECRELongDesc as [MediaName]

,cast(sum(spo.SPODiscountedCost) as money) as [Spend]

,cli.CLILongDesc AS ClientName

, cld.CALCLYNumber AS BookingYear

FROM dbo.LinkHierarchyExtended AS lhe WITH(NOLOCK)

INNER JOIN dbo.ScheduleSpot AS spo (NOLOCK) ON spo.SPOID = lhe.LHESPOID

INNER JOIN dbo.ClientHierarchy AS cli WITH(NOLOCK) ON cli.CLIID = lhe.LHECLIID and cli.CLIPROID = lhe.LHEPROID

INNER JOIN dbo.Calendar AS cld WITH (NOLOCK) ON spo.SPOBookingDate30CALID = cld.CALID

where spo.SPODiscountedCost != 0 and cld.CALCLYNumber in (2013,2014)

group by lhe.LHECRELongDesc, cli.CLILongDesc,cld.CALCLYNumber

) as t1 ) as x where x.Row <= 15

order by [BookingYear] ,[MediaName],Spend

Quick question, why the NOLOCK hints? Do you have any stats proving that they are needed?