Can anyone help ?

Here's my query :
SELECT exe.OriginalName,exesess.StartTime,exesess.endtime

FROM Applications as app

JOIN Executables as exe ON exe.ApplicationID = app.ApplicationID

JOIN ExecutableSessions AS exesess ON exesess.ExecutableID =
exe.ExecutableID

JOIN ComputerSessions AS compsess ON compsess.ComputerSessionID =
exesess.ComputerSessionID

JOIN ComputerNames as cname ON cname.ComputerID = compsess.ComputerID


WHERE cname.Name = 'test2xp02'

AND cname.Domain = 'test2.local'

AND cname.Endtime IS NULL

AND app.Name = 'NONE'

and exesess.endtime is NULL

which produces :

EXPLORER.EXE, 2009-01-09 14:28:13.817, NULL
VPCMap.exe, 2009-01-09 14:28:13.910, NULL
svchost.exe, 2009-01-09 14:28:13.973, NULL
msmsgs.exe, 2009-01-09 14:28:14.317, NULL
svchost.exe,2009-01-09 14:28:14.850,NULL
svchost.exe, 2009-01-09 14:28:15.240,NULL

This is ok. But I want to refine it to produce one row for duplicate
'originalname'('svchost' in the above). The 'originalname' to be returned in
the case of a duplicate would be the one with the earliest start time.

So I want something list this :

EXPLORER.EXE, 2009-01-09 14:28:13.817, NULL
VPCMap.exe, 2009-01-09 14:28:13.910, NULL
svchost.exe, 2009-01-09 14:28:13.973, NULL
msmsgs.exe, 2009-01-09 14:28:14.317, NULL

I have been pulling my hair our with this one trying subqueries and TOP but
I'm now confused.

Can anyone offer any advice ?

Thanks in advance

Richard

On Jan 9, 10:20*am, "Rich G" <nos... (AT) nospam (DOT) com> wrote:
Is it significant that the endtime values are all NULL?? If so, why
bother? There must be something you aren't telling us.

Based on the example data shown, it looks like a GROUP BY
and MIN(exesess.StartTime)
should work.
HTH
ed

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

On Jan 9, 10:20 am, "Rich G" <nos... (AT) nospam (DOT) com> wrote:
Is it significant that the endtime values are all NULL?? If so, why
bother? There must be something you aren't telling us.

Based on the example data shown, it looks like a GROUP BY
and MIN(exesess.StartTime)
should work.
HTH
----------------------

Thanks for the reply.

The endtime values are NULL because the column represents the endtime for an
executable program which is currently executing. This is updated when the
executable finishes executing. You are right they do represent and unknown
endtime above.

I have managed to produce this query which seems to solve my problem.

Any comments/critique is welcome :

SELECT
exe.OriginalName,exe.CompanyName,exe.FileVersion,e xesess.StartTime,exesess.endtime

FROM Applications AS app

JOIN Executables AS exe ON exe.ApplicationID = app.ApplicationID

JOIN ExecutableSessions AS exesess ON exesess.ExecutableID =
exe.ExecutableID

JOIN ComputerSessions AS compsess ON compsess.ComputerSessionID =
exesess.ComputerSessionID

JOIN ComputerNames AS cname ON cname.ComputerID = compsess.ComputerID

WHERE cname.Name = @ComputerName

AND cname.Domain = @DomainName

AND cname.Endtime IS NULL

AND app.Name = 'NONE'

AND exesess.endtime IS NULL

AND exesess.StartTime = (SELECT TOP 1 exesess2.StartTime FROM

ExecutableSessions AS exesess2

JOIN ComputerSessions AS compsess2 ON compsess2.ComputerSessionID =
exesess2.ComputerSessionID

JOIN ComputerNames AS cname2 ON cname2.ComputerID = compsess2.ComputerID

WHERE exesess2.ExecutableID = exesess.ExecutableID

AND compsess2.EndTime IS NULL

AND cname2.EndTime IS NULL

AND cname2.Name = @ComputerName

AND cname2.Domain = @DomainName

ORDER BY exesess.StartTime)

Thanks Richard

"Rich G" <nospam (AT) nospam (DOT) com> skrev i nyhetsmeldingen:
2PqdnREQWIWXZvrUnZ2dnUVZ8uGdnZ2d (AT...e (DOT) net.uk ...
Hi,

Instead of joining over againg through a subquery, I would think of using
these

....
GROUP BY , , ,
HAVING MIN(exesess.StartTime) = exesess.StartTime
;

or alternatively use a PARTITION BY and then QUALIFY, if you've got support
for this in your RDBMS:

....
QUALIFY ROW_NUMBER() OVER( PARTITION BY , , , ORDER BY exesess.StartTime
ASC)=1
;

I would guess that the execution plan will be more efficient than
(correlated) subqueries.

Good Luck:-)
Hans Sundsfjord

---
Hans Chr Sundsfjord
hans.c.sundsfjord (AT) online (DOT) no