-
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!
-
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.
-
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.
-
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..
-
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.
-
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
-
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.
-
It worked! Thanks again to both of you for your help on this--Greatly Appreciated!
