Selecting the first occurence of a rowset

[dmlatham]

I require assistance with the following table:

sample cycle idtype sample_date res1 res2
------- --- ------ ----------- --- ----
6861 1 M 05/17/1999 D <NULL>
6080 1 M 11/17/2000 A P
6080 1 M 05/19/1999 A J
6140 1 C 01/06/2000 D P
6140 1 M 06/23/1999 D J
6806 1 C 08/08/2001 A P

I need to select the first occurence of each sample from the above table. My results should be:

sample cycle idtype sample_date res1 res2
------- --- ------ ----------- --- ----
6861 1 M 05/17/1999 D <NULL>
6080 1 M 11/17/2000 A P
6140 1 C 01/06/2000 D P
6806 1 C 08/08/2001 A P

ALL HELP is greatly appreciated.

Deborah

 

[stsuing]

Select *
From Sample_Table ST
WHere Sample_Date.ST = (select max(sample_date)
FROM Sample_table
Where sample = sample.ST)

 

[dmlatham]

Thanks for help but I also need to take the idtype field in consideration along with the date. In my query that creates this table I am ordering by

sample, idtype, sampledate desc

The sample date is only part of the ordering, I need to include the idtype also. Any clues?

 

[dmlatham]

Actually, I just found that if I perform the same query that you gave me but used min(idtype) and captured that into a temp table, I can then perform your query and get the correct data. Thanks stsuing. Your help is appreciated.

 

[pyttviper]

THis example :

sample   cycle  idtype     sample_date   res1    res2
-------  ---    ------     -----------   ---     
6861      1        M        05/17/1999      D   
6080      1        M        11/17/2000      A         P
6140      1        C        01/06/2000      D         P
6806      1        C        08/08/2001      A

doesn't follow your order scheme of
sample, idtype, sampledate desc
and should be

order by idtype desc, sampledate asc
or 
order by idtype desc, sample desc

either way the above query from dmlatham will give you the results you must decide how best to order them