Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    selecting only 1 rec out of duplicate unique ids

    Hi All

    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

    best regards

  2. #2
    Join Date
    Jul 2006
    Posts
    2

    Thumbs up Removing duplicate records from resultset in oracle

    Hi,
    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.
    Regards
    Sumit Soni

  3. #3
    Join Date
    Jul 2006
    Posts
    2
    Hi sumit

    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.

    best regards
    Prasad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •