select distinct problem

[poger67]

I have a table with the follow relevant columns:

str_sptid - unique auto number
str_spid - sponsor's ID number
str_prid - provider's ID number
str_date - the date

Since this is essentially a transaction log, if there are 3 transations between a provider and a sponsor, we'll have 3 rows with unique str_sptid and 3 different dates.

What I need to do is select get the earliest (by str_date)transaction id (str_sptid) for each pair of sponsor / provider relation ships.

so, with some sample data:
str_sptid str_spid str_prid str_date
100 3 5 1/1/2000
101 3 5 2/1/2000
102 4 6 2/1/2000
103 3 6 2/2/2000
104 4 6 3/1/2000

my query would return rows 100,102,103

There's a bunch of other stuff going on here, but I've stripped it down to where I'm getting hung up on the select distinct portion (I've tried subqueries, inserting into temp tables etc. and I'm just spinning wheels at this point).

 

[r937]

select str_sptid
     , str_spid
     , str_prid
     , str_date
  from daTable as X
 where str_date
     = ( select min(str_date)
           from daTable
          where str_spid = X.str_spid  
            and str_prid = X.str_prid )

r937.com | rudy.ca