Re: How do I get one record from a group for a maximum value?

From: Dieter Noeth <dnoeth_at_gmx.de>
Date: Thu, 26 Oct 2006 08:08:10 +0200
Message-ID: <ehpjcf$jjo$1@online.de>

jonathanzta_at_yahoo.com wrote:

> C1(PK) C2 C3 C4
> =====================
> 1 psn-a 10/10/2005 Y
> 3 psn-b 09/09/2009 N
> 5 psn-a 10/10/2006 Y
>

>>From the table above, I want to get columns C2, C3, and C4 for records

>
> which have the same
> value for C2 and C3 is the maximum. The results should be:
>
> 3 psn-b 09/09/2000 N
> 5 psn-a 10/10/2006 Y

Another solution using SQL:1999 OLAP functions:

select c2,c3,c4
from
  (
   select c2,c3,c4,
     row_number() over (partition by c2 order by c3 desc) rn    from tab
  ) dt
where rn = 1

Dieter Received on Thu Oct 26 2006 - 01:08:10 CDT