PL\SQL select first row from column 1 where column 2 is unique

1
votes

I just started learning SQL. I'm using an Oracle DB.

Table emp with column empno, ename, job, mgr, hiredate, sal, comm, deptno.

I want to get the first record from ename column for each deptno and ename have to be sort by asc.

The following SQL works:

Select ename
from (Select d.ename
From emp d
Where d.deptno = 10
Order by ename asc)
where rownum <= 1
Union
Select ename
from (Select d.ename
From emp d
Where d.deptno = 20
Order by ename asc)
where rownum <= 1
Union
Select ename
from (Select d.ename
From emp d
Where d.deptno = 30
Order by ename asc)
where rownum <= 1
Union
Select ename
from (Select d.ename
From emp d
Where d.deptno = 40
Order by ename asc)
where rownum <= 1

I want to optimise it so I dont need to rewrite it if I add some record to deptno column.

I tried with SELECT distinct like

SELECT ename FROM emp WHERE (SELECT distinct deptno FROM emp)
2
sql

2 Answers

0
votes

If I understand what you're trying to do correctly, the following should work:

SELECT MIN(d.ename) as ename
  FROM emp d
  GROUP BY d.deptno
  ORDER BY ename asc

SQL Fiddle here.

0
votes

Another way to do it

SELECT d.deptno, 
       (SELECT ename 
          FROM emp e 
         WHERE e.deptno = d.deptno AND ROWNUM <=1) ename
  FROM dep d

SQLFiddle