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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 26 Oct 2006 03:51:52 -0700
Message-ID: <1161859912.036637.92310@i42g2000cwa.googlegroups.com>

Charles Hooper wrote:
> 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
> FROM
> (SELECT
> C2,
> MAX(C3) MAX_C3
> FROM
> T
> GROUP BY
> C2) M,
> T
> WHERE
> M.C2=T.C2
> AND M.MAX_C3=T.C3;
>
> C1 C2 C3 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
> FROM
> T_MAX M,
> T
> WHERE
> M.C2=T.C2
> AND M.MAX_C3=T.C3;
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

One more example, using a subquery:
SELECT

  C1,
  C2,
  C3,

  C4
FROM
  T
WHERE
  (C2,C3) IN
    (SELECT

      C2,
      MAX(C3) MAX_C3
    FROM
      T
    GROUP BY
      C2);

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Oct 26 2006 - 05:51:52 CDT