Re: how/what should be the query for this result

From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
Date: Wed, 28 Jun 2006 15:36:10 +0000 (UTC)

(choudharymv@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

References:
- how/what should be the query for this result
  - From: choudharymv

Prev by Date: how/what should be the query for this result
Next by Date: constraint expression for unique keys
Previous by thread: how/what should be the query for this result
Next by thread: Re: how/what should be the query for this result
Index(es):
- Date
- Thread

Relevant Pages

Re: how/what should be the query for this result
... Cloud, this could be the answer you are looking for: ... Progress p1 ... sample o/p can be as below ...
(microsoft.public.sqlserver)
Re: how/what should be the query for this result
... sample o/p can be as below ... over (partition by p_no, b_no order by build_date desc) r ... from progress ... Michal Kuratczyk ...
(comp.databases.oracle.server)