Query to find Unique Records

[sjs4u]

Hi, I am having 3 tables A,B,C table A:- AID date 23 01/25/2010 24 01/25/2010 table B: BID AID FileName date 1 23 1.doc 01/25/2010 1.00 AM 2 23 2.doc 01/25/2010 1.05 AM 3 23 3.doc 01/25/2010 1.06 AM 4 24 4.doc 01/25/2010 2.00 AM 5 24 5.doc 01/25/2010 2.01 AM table 3 BID Status 1 E 2 M 3 P 4 P 5 P Now I want Query that I want Unique number of records whos status is 'P' that is only Last file whos status is 'P' Means I want the following output AID FileName date status 23 3.doc 01/25/2010 1.06 AM P 24 5.doc 01/25/2010 2.01 AM P Thanks

[Pranay Rana]

Try following query hope it will work for you select tablesA.AID AID,FileName,tablesA.date date,status from tablesA inner join ( SELECT FileName,BID,AID from tablesB where bid in (select max(bid) from tablesB group by aid) ) as b on tablesA.AID = b.AID inner join tablesC on b.BID = tablesC.BID

[sjs4u]

It works.... Great Thanks for your Help

[Pranay Rana]

welcome