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 <[email protected]>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=11502.10531.6
