var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
selecting only 1 rec out of duplicate unique ids
I'm the new member of this forum. I take this opportunity to say Hello to you all.
I have an table which contains the reocrds in the following manner
dealid dealdate counterparty ccy amount costcent glacc
1234 01/01/06 xyz co USD 123.00 1111
1234 01/01/06 xyz co EUR 230.00 2222
7878 30/01/06 def co NLG 300.00 444
45678 10/01/06 abc co GBP 333.00 3333
45678 10/01/06 abc co EUR 230.00 4444
45678 10/01/06 abc co USD 400.00 2222
45678 10/01/06 abc co CHF 200.00 4444
I have thousands of records in that table.
In case of multiple duplicate records, If i need to extract only the first record from this pattern (1234 & 45678), can any one kindly help with a query
thanks for your help
Removing duplicate records from resultset in oracle
You can remove the duplicate records by this way. Lets assume that your table name is deal.
select * from deal
where rowid in (select max(rowid) from deal
group by dealid);
I hope it will solved your problam.
Thanks a lot for your reply! Was travelling on biz trip and hence could not see your reply.
BTW, this query I am going to execute in MS access. When you say rowid, should this be created as seperate column in the table? I assumed this rowid as dealid and ran the query by replacing rowid with dealid. Query did not isolate the duplicates.
Thanks in advance for your reply.