Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: select most recent distinct of a composite value
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
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