Posted by Erland Sommarskog on 10/02/70 11:51

(choudharymv@gmail.com) writes:
> I have 1 table "Progress"
>
> P_no b_no status build_date
> ----------------------------------------------------------------
> 25 1 First_slab 2006/4/5
> 25 1 second slab 2006/5/6
> 25 2 first slab 2006/1/2
> 25 2 third slab 2006/2/3
>
> o/p should be as
> Pno,bno, status, max(build_date)
>
> sample o/p can be as below
> 25 1 second slab 2006/5/6
> 25 2 third slab 2006/2/3

SELECT a.pno, a.bno, a.status, a.build_date
FROM progress a
JOIN (SELECT pno, bno, build_date = MAX(build_date)
FROM progress
GROUP BY BY pno, bno) AS b ON a.pno = b.pno
AND a.bno = p.bno
AND a.build_date = b.build_date


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]