hi vijay, You can use order by & group by commands in the queries..
Thanks and Regs, - Aman. On Mon, 19 Jan 2004, Vijay Patel wrote: > 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 > > > 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. > > > __________________________________ > Do you Yahoo!? > Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes > http://hotjobs.sweepstakes.yahoo.com/signingbonus > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
