Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: select most recent distinct of a composite value

Re: select most recent distinct of a composite value

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 28 Jul 2006 16:28:45 -0700
Message-ID: <1154129325.766524.216660@p79g2000cwp.googlegroups.com>


Gregory Bloom wrote:
> I have a (huge) table with 30 columns, and three of those columns are
> AREA_CODE, PREFIX, STATION, while another column is BILL_DATE. I want
> to select only the records having the most recent BILL_DATE for each
> distinct phone number (where phone number is a composite of
> AREA_CODE+PREFIX+STATION). What would be the most efficient way?

I would suggest looking at an inline view. Assume that your table name is PHONE_LOG:

SELECT
  AREA_CODE,
  PREFIX,
  STATION,
  MAX(BILL_DATE) BILL_DATE
FROM
  PHONE_LOG
GROUP BY
  AREA_CODE,
  PREFIX,
  STATION; The above retrieves the date of the most recent call for each phone number. Now, move it into an inline view:

SELECT

  PL2.EMPLOYEE_NAME,
  PL2.EMPLOYEE_ID,
  PL2.AREA_CODE,
  PL2.PREFIX,
  PL2.STATION,
  PL2.BILL_DATE

FROM
  (SELECT
    AREA_CODE,
    PREFIX,
    STATION,
    MAX(BILL_DATE) BILL_DATE
  FROM
    PHONE_LOG
  GROUP BY
    AREA_CODE,
    PREFIX,
    STATION) MR,
  PHONE_LOG PL2
WHERE
  MR.AREA_CODE=PL2.AREA_CODE
  AND MR.PREFIX=PL2.PREFIX
  AND MR.STATION=PL2.STATION
  AND MR.BILL_DATE=PL2.BILL_DATE;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Jul 28 2006 - 18:28:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US