Select first row of duplicate rows?

[agilentfg]

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!

[rcherch]

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.

[agilentfg]

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.

[rcherch]

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..

[agilentfg]

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.

[rcherch]

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

[kmesser]

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.

[agilentfg]

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