Link to home
Start Free TrialLog in
Avatar of sanate
sanate

asked on

find unique value from a three way join

Trying to capture processes taking io and need to select distiinct spid from the query.  Was trying to select it into a temp table, but not allowed due to 'NULL' values.  Using distinct also isn't helping.  I must be doing my join incorrectly but unsure how to proceed... it has to be something elementary.

     select convert (char(19), getdate()),
                convert (char(15), b.name) as login,
                convert (char(15), c.name) as dbname,
                convert  (char(4), a.spid) as spid,
                                             a.status,
                                             acmd,
                                             a.physical_io,
               convert  (char(5), a.cpu) as cpu,
               convert  (char(5), a.blocked) as block,
               convert  (char(5), a.time_blocked) as time

    from master..sysprocesses   a,
            master..syslogins           b,
            master..sysdatabases   c

    where physical_io > 0
Avatar of RPCIT
RPCIT

The SQL you wrote has some basic problems that I see, for instance you don't have any relations defined, but this could just be because I use MSSQL 2000 and 2005.  

But regardless, you should be able to wrap the whole thing (assuming your happy with the results) with
   SELECT DISTINCT spid FROM ( YOUR_SQL_HERE ) subQueryAlias

hope this helps.
SELECT DISTINCT spid FROM (
	       select convert (char(19) , getdate() )AS _now,
                convert (char(15), b.name) as login,
                convert (char(15), c.name) as dbname,
                convert  (char(4), a.spid) as spid,
                                             a.status,
                                             a.cmd,
                                             a.physical_io,
               convert  (char(5), a.cpu) as cpu,
               convert  (char(5), a.blocked) as block--,
               --convert  (char(5), a.time_blocked) as time
 
    from master..sysprocesses   a,
            master..syslogins           b,
            master..sysdatabases   c
 
    where physical_io > 0) sub

Open in new window

Avatar of sanate

ASKER

Thanks for looking into this.  But we've been having some issues tracking logins giving the databases angst.  This solution does indeed work for isolating unique spids... but also need to capture the other columns in the select statement.  By the unique statement I'm getting a row returned for every database... so its returning in this case 15 rows for every spid.
you just need to add the fields that you want to see, and then some aggregate function for the others.  If you want all the fields, then distict will only work where all the fields are identical.

SELECT DISTICT spid, MAX(Login) as Login FROM (.....) alias GROUP BY spid

would do a rollup, and only give you spids, but you would lost detail on the login field.
ASKER CERTIFIED SOLUTION
Avatar of grant300
grant300

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
Avatar of sanate

ASKER

Thanks much, don't know why I was drawing a blank on that.  Must have wasted half of yesterday on it.