Home » SQL & PL/SQL » SQL & PL/SQL » Distinct jobs
Distinct jobs [message #275989] Tue, 23 October 2007 10:19 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

I'm tring to find a query where I need to have all the details in my EMP table but distinct jobs, like

JOB EMPNO ENAME MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------
CLERK 1111 SMITH 7698 17-dec-81 2000 500 10

ANALYST 7788 ALLEN 7902 01-jan-80 2033 20

MANAGER 1111 WARD 7698 10-dec-82 2500 30

PRESIDENT 1111 MIKE 7698 11-jun-81 1000 500 10

SALESMAN 1111 ROB 7934 02-feb-81 3000 200 20

I hope its clear , as I mentioned above I'm looking for the output in this format where I have the distinct jobs along with all details of EMP table.

looking forward for ur help.

Regards,

Raj
Re: Distinct jobs [message #275991 is a reply to message #275989] Tue, 23 October 2007 10:21 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
It is far from clear.

Please format your post and give an example of what output you would like.
Re: Distinct jobs [message #275993 is a reply to message #275989] Tue, 23 October 2007 10:26 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Shocked
Give us correct details.

Or else do like this.

select distinct JOB,EMPNO,ENAME,HIREDATE ,DEPTNO,SAL from emp
where Ename in('SMITH','ALLEN','WARD','MIKE','ROB');


Thanx & Reds
Thangam
Re: Distinct jobs [message #276001 is a reply to message #275989] Tue, 23 October 2007 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to previous answers:
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Distinct jobs [message #276020 is a reply to message #275991] Tue, 23 October 2007 12:29 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello,

I'm just looking for the distinct jobs in my employee table. And along with that all the other details of the employee table , like the empno,ename,sal,comm,mgr,deptno and hiredate.

JOB EMPNO ENAME MGR HIREDATE SAL COMM DEPTNO
------------------------------------------------------------------

Here no where I want the duplicates of the jobs, like only one CLERK,ANALYST,MANAGER,SALESMAN and PRESIDENT.

So, atleast now.

Regards,

Raj
Re: Distinct jobs [message #276085 is a reply to message #276020] Tue, 23 October 2007 20:16 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Your explanation hardly answers any of the queries seeking a clarification. But I'll take a swipe at this. IMO, you are asking for a list of all jobs and details of any employee having that job. As long as your EMP table does not have two records having the same JOB, a simple Select can do it.

However, since that's not the case, you need to decide which record to pull up in case there are multiple employees with the same job. You might want just one, but unless you put it in the query the Optimiser wont know what search criteria you are looking at
Re: Distinct jobs [message #276088 is a reply to message #276085] Tue, 23 October 2007 22:02 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello ,

Actually I'm just looking for all the distinct jobs of my employee table and along with that all the other details of my employee table(empno,ename,comm,sal,mgr,deptno and hiredate). But I dont want any duplications of my jobs.

Atleast now,I think it makes sense please.

Regards,

Raj
Re: Distinct jobs [message #276096 is a reply to message #276088] Tue, 23 October 2007 23:25 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How did you picture the results?
You want to display distinct jobs, but you do want to add every detail. What would you want to show if two employees have the same job?
Previous Topic: cannot perform a dml operation inside a query
Next Topic: Subtract two dates to get years,months and days
Goto Forum:
  


Current Time: Sat Dec 10 18:23:56 CST 2016

Total time taken to generate the page: 0.07374 seconds