Oracle

DISTINCT on One Column Only

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Sep 2009

Posts: 8

Reputation: Billingsley is an unknown quantity at this point

Solved Threads: 0

Billingsley Billingsley is offline

Offline

Newbie Poster

DISTINCT on One Column Only

Sep 16th, 2009

Hello,
I've been trying to find a way, using Oracle Express, to return more than one column but have only one column be distinct. The following will make all three columns distinct. I want only the Job_ID column to be distinct. Can this be done in Oracle?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT DISTINCT Job_ID, Employee_ID, Last_Name
FROM Employees 
ORDER BY Last_Name
SELECT DISTINCT Job_ID, Employee_ID, Last_Name
FROM Employees 
ORDER BY Last_Name

Thanks Much,
Lewis

Last edited by peter_budo; Sep 22nd, 2009 at 9:24 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: DISTINCT on One Column Only

Sep 21st, 2009

Try something like this.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT DISTINCT emp.Job_ID, a.Employee_ID, a.Last_Name
FROM Employees emp
JOIN (SELECT job_id,employee_id,Lastname 
        FROM Employees)a on emp.Job_id = a.Job_id
ORDER BY a.Last_Name
SELECT DISTINCT emp.Job_ID, a.Employee_ID, a.Last_Name
FROM Employees emp
Join (Select job_id,employee_id,Lastname 
        From Employees)a on emp.Job_id = a.Job_id
ORDER BY a.Last_Name

•

Join Date: Sep 2009

Posts: 8

Reputation: Billingsley is an unknown quantity at this point

Solved Threads: 0

Billingsley Billingsley is offline

Offline

Newbie Poster

Re: DISTINCT on One Column Only

Sep 22nd, 2009

Thank you CGYROB. But, your solution doesn't seem to limit the recordset. It returns the same thing as if I removed the DISTINCT and the subquery. And when I use your code and only take out the DISTINCT, I get something that looks like a UNION ALL. Please let me know if you have any other ideas, but it might be that this just can't be done.

Thanks,
Lewis

•

Originally Posted by cgyrob

Try something like this.
Help with Code Tags

sql Syntax (Toggle Plain Text)
SELECT DISTINCT emp.Job_ID, a.Employee_ID, a.Last_Name
FROM Employees emp
JOIN (SELECT job_id,employee_id,Last_name 
        FROM Employees)a on emp.Job_id = a.Job_id
ORDER BY a.Last_Name
SELECT DISTINCT emp.Job_ID, a.Employee_ID, a.Last_Name FROM Employees emp Join (Select job_id,employee_id,Last_name From Employees)a on emp.Job_id = a.Job_id ORDER BY a.Last_Name

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: DISTINCT on One Column Only

Sep 22nd, 2009

I'm not quite sure what you are trying to achieve.

If you just want the job_id then grab it seperately, if you want to see all the employees associated which each job_id you have to return all rows for each job_id, unless you have exact duplicate rows which would not make sense in your db design.

What you are asking to achieve doesn't seem to make any sense without context of your ultimate goal.

•

Join Date: Sep 2009

Posts: 8

Reputation: Billingsley is an unknown quantity at this point

Solved Threads: 0

Billingsley Billingsley is offline

Offline

Newbie Poster

Re: DISTINCT on One Column Only

Sep 22nd, 2009

Actually, the example I provided was not a good one because the job_ID is the only one in the database that has duplicate values. But if you could pretend that the other columns had some duplicate values that might make the task make more sense. I'm not doing this for a current project, but rather for my own knowledge. But, I do remember needing to do this in the past. So, the idea is simply to make the job_id DISTINCT, but not the other columns. I doesn't matter how I do that. It could be by subquery or query of queries, whatever. But, I can't see a way. I saw some posts when I did an Internet search but none were for Oracle, and I'm not convinced they worked for MySQL or whatever database either.

Thanks much,
Lewis

•

Originally Posted by cgyrob

I'm not quite sure what you are trying to achieve.

If you just want the job_id then grab it seperately, if you want to see all the employees associated which each job_id you have to return all rows for each job_id, unless you have exact duplicate rows which would not make sense in your db design.

What you are asking to achieve doesn't seem to make any sense without context of your ultimate goal.

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: DISTINCT on One Column Only

Sep 22nd, 2009

I know it is possible as I have done similar queries in the past using subqueries. I provided something I put together quickly, not a very good example but it does accomplish what you were inquiring about.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT DISTINCT a.JOB_ID, 
(SELECT EMPLOYEE_ID FROM EMPLOYEE b WHERE b.JOB_ID = a.JOB_ID AND rownum = 1) , 
(SELECT LAST_NAME FROM EMPLOYEE c WHERE c.JOB_ID = a.JOB_ID AND rownum = 1)
FROM EMPLOYEEE
select distinct a.JOB_ID, 
(Select EMPLOYEE_ID from EMPLOYEE b where b.JOB_ID = a.JOB_ID and rownum = 1) , 
(Select LAST_NAME from EMPLOYEE c where c.JOB_ID = a.JOB_ID and rownum = 1)
from EMPLOYEEE

•

Join Date: Sep 2009

Posts: 8

Reputation: Billingsley is an unknown quantity at this point

Solved Threads: 0

Billingsley Billingsley is offline

Offline

Newbie Poster

Re: DISTINCT on One Column Only

Sep 22nd, 2009

Thank you. I think this is what I'm looking for. But, I don't understand what the "rownum = 1" does.

Without the rownum = 1, I get this message:
ORA-01427: single-row subquery returns more than one row

I thought "rownum =1" would just return one row. So, I'm confused about the effect of using rownum. Is there any actual restriction on the recordset from using it? Or, does it just sort of cheat the system and allow us to get what I need here?

Lewis

•

Originally Posted by cgyrob

I know it is possible as I have done similar queries in the past using subqueries. I provided something I put together quickly, not a very good example but it does accomplish what you were inquiring about.
Help with Code Tags

sql Syntax (Toggle Plain Text)
SELECT DISTINCT a.JOB_ID, 
(SELECT EMPLOYEE_ID FROM EMPLOYEE b WHERE b.JOB_ID = a.JOB_ID AND rownum = 1) , 
(SELECT LAST_NAME FROM EMPLOYEE c WHERE c.JOB_ID = a.JOB_ID AND rownum = 1)
FROM EMPLOYEEE
select distinct a.JOB_ID, (Select EMPLOYEE_ID from EMPLOYEE b where b.JOB_ID = a.JOB_ID and rownum = 1) , (Select LAST_NAME from EMPLOYEE c where c.JOB_ID = a.JOB_ID and rownum = 1) from EMPLOYEEE

•

Join Date: Sep 2009

Posts: 2

Reputation: babyDBA is an unknown quantity at this point

Solved Threads: 2

babyDBA

Offline

Newbie Poster

Re: DISTINCT on One Column Only

Sep 23rd, 2009

if Employee_ID and Last_Name can be any, within a specified Job_ID,
You can also use the group functions, eg

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
SELECT Job_ID, MAX(Employee_ID), MAX(Last_Name)
FROM Employees
GROUP BY Job_ID
SELECT Job_ID, max(Employee_ID), max(Last_Name)
FROM Employees
GROUP BY Job_ID

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: DISTINCT on One Column Only

Sep 23rd, 2009

What rownum = 1 is doing is returning only the top row of employee information for each job_id. without the rownum =1 the query will return all matches which is why you get the error message.

BabyDBA's solution should return the same results and is actually a much better way to go as there is only 1 scan of the table and not 3 plus it is alot cleaner and easier to read.

So to answer your question it is possible to get the distinct value of one field while displaying multiple fields.

•

Join Date: Sep 2009

Posts: 8

Reputation: Billingsley is an unknown quantity at this point

Solved Threads: 0

Billingsley Billingsley is offline

Offline

Newbie Poster

Re: DISTINCT on One Column Only

#10

Sep 23rd, 2009

Thank you. This is valuable. So, if rownum= 1 is returning only the top row for each job_Id, what would happen if I used subqueries for all the columns in the SELECT statement, with no distinct? Actually, I did that. I can see that it only returns the first JOb_ID but it returns 12 of them, and there aren't 12 in the database with that Id. In fact, there is only one. What is happening?

Also, I'm new to this community. I noticed something about clicking a link to promote you or give you points or something. I can't remember. I certainly intend to do that, but what does it mean?

Many thanks,
Lewis

•

Originally Posted by cgyrob

What rownum = 1 is doing is returning only the top row of employee information for each job_id. without the rownum =1 the query will return all matches which is why you get the error message.

BabyDBA's solution should return the same results and is actually a much better way to go as there is only 1 scan of the table and not 3 plus it is alot cleaner and easier to read.

So to answer your question it is possible to get the distinct value of one field while displaying multiple fields.