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

Thanks in Advance

Query for getting o/p specified by you could be...

SELECT P_No, B_No, Max(Status) Status, Max(Build_Date)
FROM Progess
GROUP BY P_No, B_No
==============================================

Pls note this query is executed against the sample data specified by
you.


-Hari Sharma
NIIT Technologies, India

Hari,

You are so funny.

Cloud, this could be the answer you are looking for:

select
p1.P_no,
p1.b_no,
p1.status,
p1.build_date
from
Progress p1
where
build_date = (
select
max(build_date)
from
Progress p2
where
p2.P_no = p1.P_no and
p2.b_no = p1.b_no)

Or it can be:

select
p1.P_no,
p1.b_no,
p1.status,
p1.build_date
from
Progress p1
where
build_date = (
select
max(build_date)
from
Progress p2
where
p2.b_no = p1.b_no)

Or it can be

select
p1.P_no,
p1.b_no,
p1.status,
p1.build_date
from
Progress p1
inner join (
select
P_no P_no,
b_no b_no,
max(build_date) build_date
from
Progress
group by
P_no,
b_no) p2 on
p2.P_no = p1.P_no and
p2.b_no = p1.b_no and
p2.build_date = p1.build_date

Or it can be

select
p1.P_no,
p1.b_no,
p1.status,
p1.build_date
from
Progress p1
inner join (
select
b_no b_no,
max(build_date) build_date
from
Progress
group by
b_no) p2 on
p2.b_no = p1.b_no and
p2.build_date = p1.build_date





[quoted text, click to view]
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

Here is another ..

create table #t
(
P_no int,
b_no int,
status varchar(20),
build_date smalldatetime
)
insert into #t values (25, 1, 'First_slab','2006/4/5')
insert into #t values (25, 1, 'second slab','2006/5/6');
insert into #t values (25, 2, 'first slab','2006/1/2');
insert into #t values (25, 2, 'third slab','2006/2/3');

select a.* from #t a, #t b where
a.p_no=b.p_no and a.b_no=b.b_no and a.status>b.status


P_no b_no status build_date

----------- ----------- --------------------
------------------------------------------------------
25 1 second slab 2006-05-06 00:00:00
25 2 third slab 2006-02-03 00:00:00


~~~~~~~~~~~~~~~
Paul

[quoted text, click to view]