Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how/what should be the query for this result
choudharymv_at_gmail.com wrote:
> 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
For example:
select p_no, b_no, status, build_date from (
select p_no, b_no, status, build_date, rank()
over (partition by p_no, b_no order by build_date desc) r
from progress
) where r = 1;
Notes:
- I assume that p_no is part of the key (it's not clear)
- if there are more than one rows with the same maximal build_date
it will return all of them
-- Michal KuratczykReceived on Wed Jun 28 2006 - 09:40:16 CDT