Perplexing Problem with tsql join.

I have been trying to figure this out for some time and I am sure there
must be an easier way than using a cursor. Given the following table
(simplified example):
ID CUSTID DTE
1 1 7/1/05
2 1 9/1/05
3 2 6/1/05
4 2 10/25/06
5 3 5/2/05
6 3 5/2/06
7 4 5/5/05
Return the max dte value record for each custid
So ie the return recordset would contain the following:
ID CUSTID DTE
2 1 9/1/05
4 2 10/25/06
6 3 5/2/06
7 4 5/5/05
Thanks in advance.
PaulYou could have more 1 or more records for each custid in my example
table I forgot to create on custid with 3 records.|||The following would work of course but what if you had another column
that could not be calculated by an aggregate column.
select max(id) id,cid,max(dte) dte from fred group by cid
for example lets add the folowing random values to column rnd
ID CUSTID DTE RND
1 1 7/1/05 32
2 1 9/1/05 42
3 2 6/1/05 68
4 2 10/25/06 2
5 3 5/2/05 5
6 3 5/2/06 9
7 4 5/5/05 3
8 3 6/2/06 7
So the return set would now include
ID CUSTID DTE RND
2 1 9/1/05 42
4 2 10/25/06 2
8 3 6/2/06 7
7 4 5/5/05 3|||Try,
select a.*
from t1 as a inner join (select custid, max(dte) as max_dte from t1 group by
custid) as b on a.custid = b.custid and a.dte = b.max_dte
AMB
"firebalrog" wrote:

> I have been trying to figure this out for some time and I am sure there
> must be an easier way than using a cursor. Given the following table
> (simplified example):
> ID CUSTID DTE
> 1 1 7/1/05
> 2 1 9/1/05
> 3 2 6/1/05
> 4 2 10/25/06
> 5 3 5/2/05
> 6 3 5/2/06
> 7 4 5/5/05
> Return the max dte value record for each custid
> So ie the return recordset would contain the following:
> ID CUSTID DTE
> 2 1 9/1/05
> 4 2 10/25/06
> 6 3 5/2/06
> 7 4 5/5/05
> Thanks in advance.
> Paul
>|||Thanks for the quick response. That is exactly what I needed. I forgot
you could use a query as part of the join. And even if I did I don't
think that I would have thought of using it in that way. Thats
beautiful. Now I will have to go through all my stored procedures and
check for places where I was using a cursor method to look for those
records in that type of situation.
Thanks again.