Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Oracle Grouping 2

Status
Not open for further replies.

moonoo

Programmer
Joined
May 17, 2000
Messages
62
Location
US
Hi ,
Suppose i have two tables DEPT(DEPTID , DNAME) and EMP(EMPID,ENAME , DEPTID) and the two tables are joined by
DEPTID .
What is the query to get a result of the following type.

DEPTID EMPID
-----------------
10 1109
2234
2456

20 2321
2212

The DeptID should not be repeated in the Result(Group By)
How do i achive this..

 
try this in sqlplus

break on deptid
select d.deptid deptid, e.empid from emp e, dept d where e.deptid = d.deptid order by 1



 
Select deptid, empid from emp group by deptid;

but I am not sure why empid is not always asending

I tried to remain child-like, all I acheived was childish.
 
Actually i wanted to do this in a single query not using any reporting.
 
Jimbo, unless I am misunderstanding your puzzlement ("...I am not sure why empid is not always ascending..."), the way to ensure that it is ascending is to changed the code to read:

Select deptid, empid
from emp
group by deptid
order by deptid, empid;

Dave

 
But the main purpose was not to get the deptid repated which happens in this case . Plese tell me how to get rid of the repeatation.
 
I you want pre-programmed group suppression (i.e., not printing the deptid) using Oracle tools, then you either do it in SQL*Plus with the command "break on deptid", or you do it in PL/SQL, writing the procedural code to suppress deptid except on the first row. I'm not aware of how to make that happen with straight SQL. Anyone? Anyone?

Dave
 
Of course, it's possible, but not too simple:

select e2.deptid, e1.empid
from emp e1
, (select deptid, min(empid) empid from emp group by deptid) e2
where e1.deptid=e2.deptid(+) and e1.empid=e2.empid(+)

Regards, Dima
 
try this in sqlplus

break on deptid
select d.deptid deptid, e.empid from emp e, dept d where e.deptid = d.deptid order by 1



 
Yes, RBeemadi, you are just as clever the second time as you were on the first copy of your code, above. But you see, Dima's assignment was to display group suppression WITHOUT SQL*PLUS'S "BREAK ON..." functionality.
 
I still do not understand why empid is not in asending order in the example or how prevent it in our code. I can only hope the example is wrong.

I tried to remain child-like, all I acheived was childish.
 
Jimbopalmer, why should it be ascending?


Regards, Dima
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top