DISTINCT on One Column Only

[Billingsley]

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?

SELECT DISTINCT Job_ID, Employee_ID, Last_Name
FROM Employees 
ORDER BY Last_Name

Thanks Much,
Lewis

[cgyrob]

Try something like this.

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

[Billingsley]

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

Try something like this.

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

[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.

[Billingsley]

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

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.

[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.

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

[Billingsley]

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

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.

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

[babyDBA]

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

SELECT Job_ID, max(Employee_ID), max(Last_Name)
FROM Employees
GROUP BY Job_ID

[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.

[Billingsley]

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

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.

[Billingsley]

What if I put two columns without subqueries? What would that return? Would it work? And, could I do the same thing using the group functions with 2 colulmns as DISTINCT?

Thanks again,
Lewis

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

[cgyrob]

By only doing subqueries you basically created a cartesian join. It displayed a record for every possible permutation of the query.

There is a simple explantion of cartesian (cross joins) here.

http://www.dba-oracle.com/t_garmany_9_sql_cross_join.htm

As for the community. It is expected when a question you ask is solved you set the thread to solved.

Reputation points can be given as well by clicking on the link below the user name. I beleive your reputation altering power is determined by your own reputation and number of threads participated in as well as solved, possibly the reason it is encouraged to mark threads solved when complete as well as keeping the site clean.

You can see what you reputation altering power is by looking in your account by clicking on your own link.

Have a good one.

[Billingsley]

By only doing subqueries you basically created a cartesian join. It displayed a record for every possible permutation of the query.

There is a simple explantion of cartesian (cross joins) here.

http://www.dba-oracle.com/t_garmany_9_sql_cross_join.htm

As for the community. It is expected when a question you ask is solved you set the thread to solved.

Reputation points can be given as well by clicking on the link below the user name. I beleive your reputation altering power is determined by your own reputation and number of threads participated in as well as solved, possibly the reason it is encouraged to mark threads solved when complete as well as keeping the site clean.

You can see what you reputation altering power is by looking in your account by clicking on your own link.

Have a good one.

Does your reputation mean anything, or is it just a fun thing? Also, should I add to the reputation after each post?

Thanks,
Lewis

[cgyrob]

When you have a higher reputation you get more greeen squares in your header file (I guess it is supposed to signify how competent the responders are) as well I think it might have a reflection on your own altering power (I really haven't figured out how the reputation altering power is assessed but it would make sense).

I beleive it is more important to most that the thread is marked as solved as new members have very limited reputation altering power. Geeks love to help they just need some sort of external gratification for it.

Hiope that helps.

[Billingsley]

Well, I certainly appreciate the help you've given me. I've added to your reputation (if I can indeed, add to it???) and marked it solved.

Thanks again,
Lewis

[Quacau corp]

SELECT song_ID,songs.fullname, singers.fullname
FROM music JOIN songs ON songs.ID = music.song_ID
JOIN singers ON singers.ID = music.singer_ID
GROUP BY songs.fullname

You MUST HAVE "GROUP BY" clause to get it works.

Hope this helps.