TSQL problem - please help!!!!

I have a problem that I can't solve. Any help would be greatly appreciated.

I need to return one row with the following information.(see eg. table below)

Matter#(group by), timekeeper, most recent rate ( based on date field)



Matter Timekeeper Rate Date
-------------------------------------------------
23 0045 120.00 01/01/1999
23 0045 110.00 01/01/1998
23 0045 100.00 01/01/1997
25 0023 150.00 01/01/1998
25 0072 130.00 01/01/1999
23 0052 90.00 01/01/1996


I am looking for a return like this:

Matter Timekeeper Rate Date
-------------------------------------------------
23 0045 120.00 01/01/1999
23 0052 90.00 01/01/1996
25 0023 150.00 01/01/1998
25 0072 130.00 01/01/1999

Suppose your table name is [test]
try this:

select * from test tt
where ( rate =(select top 1 t.rate from test t where tt.matter= t.matter and tt.timekeeper=t.timekeeper)
and date =(select top 1 t.date from test t where tt.matter= t.matter and tt.timekeeper=t.timekeeper) )
order by tt.matter, tt.timekeeper

hope this helps

Jonathan

------------
Jason Fitch at 7/12/99 3:13:51 PM


I have a problem that I can't solve. Any help would be greatly appreciated.

I need to return one row with the following information.(see eg. table below)

Matter#(group by), timekeeper, most recent rate ( based on date field)



Matter Timekeeper Rate Date
-------------------------------------------------
23 0045 120.00 01/01/1999
23 0045 110.00 01/01/1998
23 0045 100.00 01/01/1997
25 0023 150.00 01/01/1998
25 0072 130.00 01/01/1999
23 0052 90.00 01/01/1996


I am looking for a return like this:

Matter Timekeeper Rate Date
-------------------------------------------------
23 0045 120.00 01/01/1999
23 0052 90.00 01/01/1996
25 0023 150.00 01/01/1998
25 0072 130.00 01/01/1999