469,148 Members | 1,539 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

To Re-write complex query with JOINS

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.
Jul 19 '05 #1
0 1049

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Nel | last post: by
4 posts views Thread by Craig Bailey | last post: by
1 post views Thread by Chris | last post: by
4 posts views Thread by Alan Walkington | last post: by
1 post views Thread by John Ryan | last post: by
10 posts views Thread by James | last post: by
8 posts views Thread by Lothar Scholz | last post: by
3 posts views Thread by presspley | last post: by
2 posts views Thread by sky2070 | last post: by
1 post views Thread by Brian | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.