To Re-write complex query with JOINS

vijay96238

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.

mtimdog

select distinct recordid, effectivedate, othertableid, value from test order by effectivedate desc

If this give the wrong output, order by effectivedate asc.

I always forget which way a date is ordered.

Lars_Berg

But I have read in the manual of MySQL
that any "SUBQUERY" SQL statement can be written with
proper "JOINS".

Well, you can't. Actually Mysql did not claim this, but they do claim to much in this respect.

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html