Link to home
Start Free TrialLog in
Avatar of Allen Pitts
Allen PittsFlag for United States of America

asked on

Nested DISTINCT SELECT

Hello Expert

Have a T-SQL big query that works , copied below.
The big query shows the reports that have not been run for a year.
It is returning  200k plus rows with some of the report names
repeated over and over.

So would like to remove the duplicated names. So I wrote

Select Distinct ctl.Name
from
(<the big query>)

But SSMS complains about the trailing parenthesis.

How can one do this using distinct or
Group BY or such?

Thanks.

Allen in Dallas


+++++start big query+++++++++++
Select  
ctl.Name AS ReportName,ctl.Name,
 exl.TimeStart,
      exl.TimeEnd,
       exl.Status,
       exl.ByteCount,
       exl.RowsCount,
       ctl.Path
FROM      Subscription.dbo.SSRSExecutionLog exl
LEFT JOIN ReportServer.dbo.Catalog ctl ON exl.ReportID = ctl.ItemID
LEFT JOIN Subscription.dbo.SSRSRequestTypes srt ON exl.RequestType = srt.RequestType
LEFT JOIN Subscription.dbo.SSRSReportTypes  rty ON ctl.Type = rty.ReportTypeID
LEFT JOIN Subscription.dbo.SSRSSourceTypes sst ON exl.Source = sst.SourceTypeID
WHERE IsNull(srt.RequestDesc, exl.RequestType) != 'System'
AND   exl.Format = 'HTML4.0'
AND   IsNull(rty.ReportType, ctl.Type) = 'Report'
AND   exl.TimeStart > '2014-10-19'

++++++end big query++++++++++++++++++
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial