Home » SQL & PL/SQL » SQL & PL/SQL » distinct values of more than one column
distinct values of more than one column [message #229340] Fri, 06 April 2007 16:27 Go to next message
smitakrishna_dv
Messages: 1
Registered: March 2007
Junior Member
Hi,

Is there a way to find distinct values of more than column in a single command?

For example, I want to list the distinct jobs and dept numbers in a single query, how do I do it?

select distinct job from emp;
select distinct deptno from dept;

I want to have both in a single query. IS there a way?

Thanks and Regards
Smita
Re: distinct values of more than one column [message #229341 is a reply to message #229340] Fri, 06 April 2007 17:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Include UNION between them.
Re: distinct values of more than one column [message #229618 is a reply to message #229340] Mon, 09 April 2007 07:10 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
You aren't clear so it's possible that you might want the unique combos of job and departments.

select distinct
   e.job, 
   d.deptno
from 
   emp e,
   dept d
where e.deptno (+) = d.deptno
order by job, deptno



which gives...

JOB       DEPTNO
-------   ------
ANALYST   20     
CLERK     10     
CLERK     20     
CLERK     30     
MANAGER   10     
MANAGER   20     
MANAGER   30     
PRESIDENT 10     
SALESMAN  30     
          40     



The query uses an outer join to get any departments without employees.
Re: distinct values of more than one column [message #229644 is a reply to message #229618] Mon, 09 April 2007 08:22 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And where do you see in your answer the list of jobs and deptno?
I see a list of some couples (job,deptno) but not all of them.

Regards
Michel

Previous Topic: calculate hours:minutes:seconds (merged 4 cross-posts)
Next Topic: Surrogate key
Goto Forum:
  


Current Time: Fri Dec 09 07:42:29 CST 2016

Total time taken to generate the page: 0.15873 seconds