Binaryworld - Grouping Problem: How to get single record from group with minimum val ... [ SQL Server -> T-SQL ]

Assume that you have multiple record of customer transactions but you only want one record for each customer with minimum transaction date field... How would you do it ????

Please check the following query

Click here to copy the following block

create table #tmp(cust_code varchar(10),cdate datetime,amount money)

insert into #tmp values('BW','1/1/07',1000)
insert into #tmp values('BW','1/2/07',500)
insert into #tmp values('BW','1/3/07',4000)
insert into #tmp values('MS','1/1/07',23000)
insert into #tmp values('MS','1/2/07',10)

select a.cust_code,a.cdate,a.amount
from (select t.cust_code,t.cdate,t.amount
  from #tmp t where cdate in (select min(cdate) from #tmp where cust_code=t.cust_code)
  group by t.cust_code,t.cdate,t.amount
  ) a
group by a.cust_code,a.cdate,a.amount

go

drop table #tmp

/*
Output :

cust_code cdate          amount
---------- ----------------------- ---------------------
BW     2007-01-01 00:00:00.000 1000.00
MS     2007-01-01 00:00:00.000 23000.00

*/