Select first row of duplicate rows?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Select first row of duplicate rows?

Hybrid View

  1. #1
    Join Date
    May 2001
    Posts
    28

    Question

    It seems simple, but I need some help to write a query that will only select the first row (columns a,b,c,d,e) of a group of similar rows. Example: an employee can have one or more records (columns a is the employee number), but I only want to extract the first row for each employee. See below:

    Table

    Emp, Fname, Lname,itemNo, ItemDesc
    1 joe smith 123 test part
    1 joe smith 134 test part 2
    1 joe smith 145 test part 3
    2 bill white 111 partb
    3 dave green 123 test part
    3 dave green 111 partb

    These are the rows I'm trying to extract:
    1 joe smith 123 test part
    2 bill white 111 partb
    3 dave green 111 partb

    Please help. Thanks!




  2. #2
    Join Date
    May 2001
    Posts
    70
    Do you have a create date as part of the table? If so, you can check for the max(create date).

    Could you supply the table definition so we can help.

  3. #3
    Join Date
    May 2001
    Posts
    28
    Correction, The last line should be as follows:

    1 joe smith 123 test part
    2 bill white 111 partb
    3 dave green 123 test part

    Thanks for your reply. There are no dates in the table. The table definition is as follows:

    CREATE TABLE FGTAB.Product_Info
    (
    Emp_nr NUMBER,
    Last_name VARCHAR2(50),
    First_name VARCHAR2(50),
    Prod_cd VARCHAR2(16),
    Prod_descr VARCHAR(100),
    Misc_notes VARCHAR(100)
    )

    There are no keys in the table. The table holds about 200 entries.

    Thanks again for your help.



  4. #4
    Join Date
    May 2001
    Posts
    70
    Ok..this is going to be fun, now isn't it.

    First of all, put a create_dt on the table with a default of sysdate. Make your life easier for the future.

    I would look at rownum rank if your running 8.1.6.

    select min(rs), emp_nr, last_name,
    first_name, prod_cd from
    (select rownum rank rs, emp_nr, last_name,
    first_name, prod_cd from
    (select emp_nr, last_name, first_name, prod_cd,
    from fgtab.product_info))
    group by emp_nr, last_name, first_name, prod_cd

    This might work..



  5. #5
    Join Date
    May 2001
    Posts
    28
    Should the "rownum rank rs" work on the 8.1.5.0.0 release as well? It is giving me an error on the 'rank' qualifier.

    I agree with you about adding a date field to the table, but we do not own the table and we are only using it to report the information that we need.

    Thanks again for your help.

  6. #6
    Join Date
    May 2001
    Posts
    70
    I think rownum rank started on version 8.1.6.

    The only other thing I can think of is using a PL/SQL Package that use a For Cursor Loop with some sort of first time identifier.

    Good Luck

  7. #7
    Join Date
    Aug 2000
    Posts
    462
    You could use:

    select * from mytable where rowid in (select min(rowid) from mytable group by emp);

    Of course, this doesn't guarantee any certain order, but merely A record for each employee. Without a date/time stamp, there's no way to do it.
    Oracle DBA and Developer

  8. #8
    Join Date
    May 2001
    Posts
    28

    Thumbs up

    It worked! Thanks again to both of you for your help on this--Greatly Appreciated!

Posting Permissions

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


Click Here to Expand Forum to Full Width