Insert data from emp to emp1

[aph]

Hi,
Please consider the following example:

Let's say we have table emp with the following values:
SQL> select deptno, ename, job, sal from emp;

DEPTNO ENAME JOB SAL
--------- ---------- --------- ---------
20 SMITH CLERK 800
30 ALLEN SALESMAN 1600
30 WARD SALESMAN 1250
20 JONES MANAGER 2975
30 MARTIN SALESMAN 1250
30 BLAKE MANAGER 2850
10 CLARK MANAGER 2450
20 SCOTT ANALYST 3000
10 KING PRESIDENT 5000
30 TURNER SALESMAN 1500
20 ADAMS CLERK 1100
30 JAMES CLERK 950
20 FORD ANALYST 3000
10 MILLER CLERK 1300

14 rows selected.

Requirement:
We need to pick 1st row of each deptno from emp and insert into emp1 table. 1st mean actual 1st row of emp table.

E.g.:
20 SMITH CLERK 800
30 ALLEN SALESMAN 1600
10 CLARK MANAGER 2450

Now the above three rows should be insert in emp1 table, means emp1 containing 3 rows.

I have tried too many ways to get this done, but fail to achieve the query. It would be nice if you could provide some solution.

Thanks.

[paul]

Requirement:
We need to pick 1st row of each deptno from emp and insert into emp1 table. 1st mean actual 1st row of emp table.


What is an "actual 1st row"? The first row for that deptno ever entered into the table? The first row for a deptno that gets returned? Unless you explicitly order the results then you should not infer anything from what gets returned first.

That said, try this:

select a.deptno, a.ename, a.job, a.sal
from emp a
where rowid = (select min(rowid)
from emp e
where e.deptno = a.deptno)


--
Paul

[aph]

Good Excellent, This is what I was looking.

Thanks for you time.