Single Column DISTINCT in Multiple Column SELECT

[billingsly]

Hello,
   I've been searching for a way in SQL to make one column distinct when more than one column is listed in the SELECT statement in Oracle. I've seen solutions on the Internet, but none are for Oracle (if in fact the one's I saw actuallly work).  Is it possible to do this with Oracle?

Thanks Much,
Lewis


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3241
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Peter Boughton]

You've not explained very well what you're trying to do - maybe
illustrate it with some dummy data to show the structure you have and
the one you want?

Also, post the non-Oracle solutions you found, and details of the
errors you received when trying them.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3242
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Joe Graves]

This sound like you need to use a "group by" or a "where" clause, rather
than a distinct.  However you could do this using a sub select or a temp
table.


-----Original Message-----
From: Lewis Billingsley [mailto:Billingsly@...]
Sent: Saturday, September 05, 2009 4:49 PM
To: sql
Subject: Single Column DISTINCT in Multiple Column SELECT


Hello,
   I've been searching for a way in SQL to make one column distinct when
more than one column is listed in the SELECT statement in Oracle. I've seen
solutions on the Internet, but none are for Oracle (if in fact the one's I
saw actuallly work).  Is it possible to do this with Oracle?

Thanks Much,
Lewis




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3243
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[billingsly]

Thank you for responding.  What I want to do is very simple, but the solution may not be.  Usually, if you use DiSTINCT in a SELECT statement, the recordset returned will give you distinct values for all columns in the SELECT statement:

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

   This will return distinct values for Job_ID, Employee_ID, and Last_Name.  I only want the Job_ID to be distinct. One solution that does not work in Oracle that was suggested was the query below.  I don't know what database it does work in.

SELECT a.Employee_ID, a.Last_Name, b.Job_ID  
FROM (SELECT distinct Last_Name, Employee_ID FROM Employees) a
RIGHT JOIN
(SELECT max(Employee_ID) AS Employee_ID, Last_Names, MAX(Job_ID) AS Job_ID  
From Employees
GROUP BY Last_Name) b
ON b.Last_Name = a.Last_Name
ORDER BY Last_Name

Thanks Much,
Lewis


>You've not explained very well what you're trying to do - maybe
>illustrate it with some dummy data to show the structure you have and
>the one you want?
>
>Also, post the non-Oracle solutions you found, and details of the
>errors you received when trying them.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3244
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Richard Dillman-2]

Your going to have to aggrigate those other 2 columns (min,
max,sum...) and then group by the Job_ID(the non aggregated item)

SELECT   DISTINCT job_id,
                  *Min*(employee_id),
                  *Min*(last_name)
FROM     employees
GROUP BY job_id
ORDER BY last_name

This would return the first instance of each Job_ID, but you are loosing any
additional employees that also use that job ID.

I guess the real question is what are you trying to get as a result?


On Wed, Sep 9, 2009 at 12:19 PM, Lewis Billingsley <Billingsly@...>wrote:

>
> Thank you for responding.  What I want to do is very simple, but the
> solution may not be.  Usually, if you use DiSTINCT in a SELECT statement,
> the recordset returned will give you distinct values for all columns in the
> SELECT statement:
>
> SELECT DISTINCT Job_ID, Employee_ID, Last_Name
> FROM Employees
> ORDER BY Last_Name
>
>   This will return distinct values for Job_ID, Employee_ID, and Last_Name.
>  I only want the Job_ID to be distinct. One solution that does not work in
> Oracle that was suggested was the query below.  I don't know what database
> it does work in.
>
> SELECT a.Employee_ID, a.Last_Name, b.Job_ID
> FROM (SELECT distinct Last_Name, Employee_ID FROM Employees) a
> RIGHT JOIN
> (SELECT max(Employee_ID) AS Employee_ID, Last_Names, MAX(Job_ID) AS Job_ID
> From Employees
> GROUP BY Last_Name) b
> ON b.Last_Name = a.Last_Name
> ORDER BY Last_Name
>
> Thanks Much,
> Lewis
>
>
> >You've not explained very well what you're trying to do - maybe
> >illustrate it with some dummy data to show the structure you have and
> >the one you want?
> >
> >Also, post the non-Oracle solutions you found, and details of the
> >errors you received when trying them.
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3245
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6