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

Take a look at min (max would work equally well) coupled with keep.

SELECT             FROM_DATE,
                           TO_DATE,
                           DURATION,
                           SOURCE,
                           DESTINATION,
                           min(ORIGIN_FILE) keep (dense_rank first order by 
rowid),
                           min(ORIGIN_ROW)  keep (dense_rank first order by 
rowid),
                           min(COMMENT)  keep (dense_rank first order by 
rowid)
 FROM                Table
 GROUP BY        FROM_DATE,
                           TO_DATE,
                           DURATION,
                           SOURCE,
                           DESTINATION
HAVING            Count(*)>1;

That will give you the values of origin_file, origin_row, and comment for the first rowid in each grouped set.

"Haximus" <e_at_t.me> wrote in message news:HYoUd.9344$hN1.7760_at_clgrps13...
> "G-Shock" <g-shock_at_g-shock.com> wrote in message
> news:4220b725$1_at_news.012.net.il...
>> 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;
>
> If the records are truly identical, shouldn't matter which one you
> select... just select one of 'em.
>
> If the GROUP BY columns are identical but not the other columns, you're
> going to have to specify additional selection criteria to specify which of
> those duplicate rows that you want... whatever it is that identifies a row
> as being 'first'
>
Received on Mon Feb 28 2005 - 00:53:40 CST