| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I get one record from a group for a maximum value?
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
>
> I tried various group by but wasn't able to make it work (perhaps I'm
> confused).
> In pseudo code this would be:
>
> Get the columns C2, C3, C4 for a particular C2 where C3 has the maximum
> value.
>
> Any suggestions (along with sample SQL are really welcome)
>
> P.S.
> This is a general query and not Oracle specific. However, I didn't find
> a better group to post this.
Oracle specific solutions follow.
CREATE TABLE T (
C1 NUMBER(22),
C2 VARCHAR2(10),
C3 DATE,
C4 VARCHAR2(1));
INSERT INTO
T
VALUES (
1,
'psn-a', '10-OCT-2005', 'Y');
INSERT INTO
T
VALUES (
3,
'psn-b', '09-SEP-2009', 'N');
INSERT INTO
T
VALUES (
5,
'psn-a', '10-OCT-2006', 'Y');
Inline view:
SELECT
T.C1, T.C2, T.C3, T.C4
========== ========== ==================== == 3 psn-b 09-SEP-2009 00:00:00 N 5 psn-a 10-OCT-2006 00:00:00 Y
Above finds the distinct values of C2 and the last date at that value of C2 and then uses the results to return specific rows from the table.
Analytic functions:
SELECT DISTINCT
FIRST_VALUE(C1) OVER (PARTITION BY C2 ORDER BY C3 DESC) C1,
C2,
FIRST_VALUE(C3) OVER (PARTITION BY C2 ORDER BY C3 DESC) C3,
FIRST_VALUE(C4) OVER (PARTITION BY C2 ORDER BY C3 DESC) C4
FROM
T;
C1 C2 C3 C4
========== ========== ==================== == 3 psn-b 09-SEP-2009 00:00:00 N 5 psn-a 10-OCT-2006 00:00:00 Y
Method that might be a bit more portable:
CREATE VIEW T_MAX AS
SELECT
C2,
MAX(C3) MAX_C3
FROM
T
GROUP BY
C2;
SELECT
T.C1, T.C2, T.C3, T.C4
 |
 |