SELECT syntax question - selecting older records

[EricBass]

I am trying to find the syntax for the following select statement and can't quite seem to figure it out. Any help would be very appreciated!

Table A
CustID, Rate, RateChgDate
1 .25 01/25/2004
1 .35 06/30/2004
2 .20 02/15/2005
3 .15 03/24/2005
3 .12 06/01/2005
..and so on

So the table can have duplicate records for a customer with different rates. I need to determine the original rate, so my code needs to logically do the following:

SELECT Rate From Table A where RateChgDate = "earliest date found for this CustID"

Any ideas will be appreciated!

-Eric

 

[gmmastros]

If RateChgDate is a SQL Server DateTime field, you can use the Min function

Select CustId, Rate, RateChgDate As EarliestDate 
From TableA 
     Inner Join 
       (Select CustId, 
               Min(RateChgDate) As EarliestDate 
       From TableA Group By CustId) As MinDate 
    On TableA.CustId = MinDate.CustId 
       And TableA.RateChgDate = MinDate.EarliestDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[EricBass]

That looks to be working like a charm.

Thank you very much for the quick response!

-Eric