Re: ??? What is the substitution for the function FIRST in ORACLE????

G-Shock wrote:

> Hi There,
>
> there is a table wich includde for examples these fields:
> FROM_DATE, TO_DATE, DURATION, SOURCE, DESTINATION, ORIGIN_FILE, ORIGIN_ROW,
> COMMENT... and so on.
>
> What i need is to find the duplicate rows by the fields: FROM_DATE, TO_DATE,
> DURATION, SOURCE, DESTINATION and
> to filter all the duplications.
> For the select as above i'll get all the unique rows as mentioned on the
> group by.
> for each row i need the values for the fields: ORIGIN_FILE, ORIGIN_ROW,
> COMMENT wich exist in the first row.
> MS-Access hes FIRST() function but Oracle ): hasn't.
>
> ??? What is the substitution for the function FIRST in ORACLE????
>
> SELECT FROM_DATE,
> TO_DATE,
> DURATION,
> SOURCE,
> DESTINATION,
> First(ORIGIN_FILE),
> First(ORIGIN_ROW),
> First(COMMENT)
> FROM Table
> GROUP BY FROM_DATE,
> TO_DATE,
> DURATION,
> SOURCE,
> DESTINATION
> HAVING Count(*)>1;
>
>
> Thanks.

As IANAL_VISTA stated there is no concept of first and last in a heap table. One can choose to eliminate the duplicate row based on the minimum or maximum ROWID but what that means is purely arbitrary. One simple way to identify duplicate records is to use the functionality already provided with Oracle when you install it.

Go to http://www.psoug.org
click on Morgan's Library
click on Constraints
scroll down to "Enable Constraint"
and take advantage of utlexcpt.sql script

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)