CODEWITHSUNDEEP
sqlsql-serversql-server-2008
AlexJP
AlexJP

Reputation: 13

SQL - Return only first row that matches


The table dbo.AllApps logs all executable that has been run it has a column named ApplicationHash which stores the exe hash value.
So if the exe has been run 10 times, there will be 10 entries in table with the same information but different timestamp.
I would like to produce a report that will only return 1 row from the 10 (with all columns time,name,desc ...) and one additional column that will provide the count of the number of times the exe has been executed.
Any suggestions?

`/* !!!!!DEFINE START AND END DATE FOR REPORT HERE!!!!!*/
DECLARE @StartDate DATETIME = 2017-01-01
DECLARE @EndDate DATETIME = 2017-01-02`

`/* Checks if the temp table #PCount exist and deletes the table if it does */
IF OBJECT_ID('tempdb..#PCount') IS NOT NULL
BEGIN
DROP TABLE #PCount
END`

`/* Performs a COUNT on the ApplicationHash entries */
SELECT ApplicationHash, COUNT(ApplicationHash) AS ProcessCount
INTO #PCount
FROM dbo.AllApps 
WHERE
TokenType = 'Elevated' 
AND ApplicationType != 'COM Class'
AND ApplicationType != 'ActiveX Control'
AND ProcessStartTime >= @StartDate
AND ProcessStartTime < @EndDAte
GROUP BY ApplicationHash`

`/* Pulls up the actual report and inserts the count value for the ApplicationHash */
SELECT #PCount.ProcessCount, 
dbo.AllApps.ApplicationHash, 
dbo.AllApps.ProcessStartTime,
dbo.AllApps.ApplicationType,
dbo.AllApps.Description, 
dbo.AllApps.Publisher, 
dbo.AllApps.ProductName, 
dbo.AllApps.ProductVersion, 
dbo.AllApps.EventDescription, 
dbo.AllApps.CommandLine, 
dbo.AllApps.FileName
FROM #PCount, dbo.AllApps 
WHERE dbo.AllApps.ApplicationHash = #PCount.ApplicationHash
AND TokenType = 'Elevated'
AND ApplicationType != 'COM Class'
AND ApplicationType != 'ActiveX Control'
AND ProcessStartTime >= @StartDate
AND ProcessStartTime < @EndDate
ORDER BY ProcessStartTime DESC`

Upvotes: 1

Views: 178

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82534

The easy way is to use a cte and row_number:

;with cte as
(
SELECT ROW_NUMBER() OVER(PARTITION BY ApplicationHash ORDER BY ProcessStartTime) as rn,
#PCount.ProcessCount, 
dbo.AllApps.ApplicationHash, 
dbo.AllApps.ProcessStartTime,
dbo.AllApps.ApplicationType,
dbo.AllApps.Description, 
dbo.AllApps.Publisher, 
dbo.AllApps.ProductName, 
dbo.AllApps.ProductVersion, 
dbo.AllApps.EventDescription, 
dbo.AllApps.CommandLine, 
dbo.AllApps.FileName
FROM #PCount
INNER JOIN dbo.AllApps ON dbo.AllApps.ApplicationHash = #PCount.ApplicationHash
WHERE TokenType = 'Elevated'
AND ApplicationType != 'COM Class'
AND ApplicationType != 'ActiveX Control'
AND ProcessStartTime >= @StartDate
AND ProcessStartTime < @EndDate
)

SELECT ProcessCount, 
       ApplicationHash, 
       ProcessStartTime,
       ApplicationType,
       Description, 
       Publisher, 
       ProductName, 
       ProductVersion, 
       EventDescription, 
       CommandLine, 
       FileName
FROM CTE
WHERE rn = 1

Please note I've changed your implicit join to an explicit join.
Read Aaron Bertrand's Bad habits to kick : using old-style JOINs to find out why.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38073

Using top with ties with row_number() to get the row with the most recent ProccessStartTime and count(*) over() for the ProcessCount.

Note: the temporary table is not needed for this solution. If the where criteria were not the same for both queries, then it would be a different story.

Update: Without knowing more about the data types and the presence of a unique id for rows on this table, I have added a case expression to include the ProcessStartTime to the partition when ApplicationHash = <None>, else a constant (@StartDate). 

declare @StartDate datetime = '20170101';
declare @EndDate datetime   = '20170102';

select top 1 with ties
    ProcessCount = count(*) over (
      partition by ApplicationHash 
        , case 
          when ApplicationHash = '<None>' 
            then ProcessStartTime
          else @StartDate
          end 
      order by ProcessStartTime desc
      )
  , ApplicationHash
  , ProcessStartTime
  , ApplicationType
  , Description
  , Publisher
  , ProductName
  , ProductVersion
  , EventDescription
  , CommandLine
  , FileName
from dbo.AllApps aa
where TokenType = 'Elevated' 
  and ApplicationType != 'com Class'
  and ApplicationType != 'ActiveX Control'
  and ProcessStartTime >= @StartDate
  and ProcessStartTime < @EndDate
order by row_number() over (
  partition by ApplicationHash
    , case 
      when ApplicationHash = '<None>' 
        then ProcessStartTime
      else @StartDate
      end
  order by ProcessStartTime desc
  )

Upvotes: 0

Related Questions