Distinct Records w/o a Distnict 1

[Signit]

I am trying to select machine_sk records based on owner_cust_id records. Essentially if a owner_cust_id record is associated with more than one machine_sk I want to get only one of the machine_sks. I have tried many variations of the last portion of my sql query and have come up empty handed. Any assistance would be greatly appreciated.

(The query does not typically return results akin to below. I want to return only the machine_sk coulmn the owner_cust_id portion is only there for illustration.)

machine_sk    owner_cust_id
69003        7307
69944        14395
71907        14395
59844        46214
68355        46214
67755        49389
71916        56253
43181        56715
43623        56715
48777        60994

declare @location_bldg_num char(30)
set @location_bldg_num = '800a'

       select a.machine_sk
         from machine a
        where a.location_bldg_num = @location_bldg_num
          and a.machine_status = 'a'
          and a.os in ('windows 2000','windows xp')
          and a.sandia_prop_num is not null
          and a.machine_sk not in (select t.machine_sk
                                     from wfa_log t
                                    where t.location_bldg_num = @location_bldg_num
                                      and t.wfa_log_id = (select max(wfa_log_id)
                                                            from wfa_log z
                                                           where a.machine_sk = z.machine_sk)
                                      and ((t.audit_customer_complete is null
                                            and t.rcd_chg_date_time > dateadd(day,-90,getdate()))
                                          or (t.audit_customer_complete is not null
                                              and t.audit_customer_complete > dateadd(day,-365,getdate()))))
          and a.machine_sk not in (select top 1 q.machine_sk
                                     from machine q
                                    where q.location_bldg_num = @location_bldg_num
                                    group by q.machine_sk
                                   having count(q.owner_cust_id) > 1)

 

[r937]

what is this part of your query for?

and a.machine_sk not in 
( select top 1 q.machine_sk
    from machine q
   where q.location_bldg_num 
       = @location_bldg_num
  group by q.machine_sk
  having count(q.owner_cust_id) > 1 )

is that an essential part of your logic, or is that the part that's supposed to get only one machine_sk per owner?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[Signit]

That's the part that is not doing what I want it to.

 

[r937]

okay, then i guess it's okay to replace it with this --

and a.machine_sk =
( select max(machine_sk)
    from machine 
   where q.owner_cust_id 
       = a.owner_cust_id )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[r937]

aargh, typo

it should be --

and a.machine_sk =
( select max(machine_sk)
    from machine 
   where owner_cust_id 
       = a.owner_cust_id )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[Signit]

That seems to work! Thank you for the elegant solution. I was pulling my hair out going down the wrong path.