Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.linkpendium.com!nntp-relay.ihug.net!ihug.co.nz!news-out.newsfeeds.com!propagator2-maxim!feed-maxim.newsfeeds.com!feed.uncensored-news.com!news.alt.net!plato.usenetgateway.com!not-for-mail
From: LKBrwn_DBA <member30625@dbforums.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: How to append information to simmilar data
Date: Fri, 08 Aug 2003 21:50:33 +0000
Organization: dBforums
Lines: 16
Sender: LKBrwn_DBA
Message-ID: <3219487.1060379433@dbforums.com>
References: <17af6360.0308080157.76fbef08@posting.google.com>
User-Agent: dBforums
X-Original-NNTP-Posting-Host: dbforums.com
X-Original-Trace: 8 Aug 2003 22:23:28 GMT, dbforums.com
X-Authenticated-User: usenetgateway
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:130390


Try this:

select stu_fnm1||'.'||stu_surn||rk username, field2,field3 from (
select stu_fnm1, stu_surn, field2, field3 ect...
     , rank() over (partition by stu_fnm1, stu_surn
                        order by stu_fnm1, stu_surn) rk
from table1
where (stu_fnm1||'.'||stu_surn) in (select (stu_fnm1||'.'||stu_surn)
from table1
GROUP BY stu_fnm1||'.'||stu_surn
HAVING Count(stu_fnm1||'.'||stu_surn)>1))
order by username

--
Posted via http://dbforums.com