To Re-write complex query with JOINS

[Vijay]

Hello friends,

I have a problem with one of the JOIN query here.
Below is a brief description of the problem.

tablename : test

RecordId EffectiveDate othertableid value
-------- ------------- ------------ -----
1 2004-01-10 1 10
2 2004-01-20 1 20
3 2004-01-20 2 70
4 2004-01-10 2 80
5 2004-01-15 1 10
6 2004-01-25 3 10
Output :
RecordId EffectiveDate othertableid value
-------- ------------- ------------ -----
2 2004-01-20 1 20
3 2004-01-20 2 70
6 2004-01-25 3 10

Now I want to use a single SQL query to find a result
where there exist one record for each unique
"othertableid" where the record selected for the
"othertableid" should be the recent one with regard to
"EffectiveDate".

That is from the above records, I want to select
Records with "RecordId" = 2 and 3 because they are the
recent one for "othertableid" = 1 and 2 respectively.
Please be sure that I want to retrive all fields
including "RecordId". The result should not depend on
any other fields but "EffectiveDate" only.

I am using MySQL 4.0.12 and it does not support
"SUBQUERIES" which is now given support in latest
MySQL edition. But I have read in the manual of MySQL
that any "SUBQUERY" SQL statement can be written with
proper "JOINS".

Can you help me.

Vijay.