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=11502.10531.6
