Need help eliminating duplicates and keeping the last record

maximus1974
maximus1974 used Ask the Experts™
on
Need a SQL statement that eliminates duplicates and chooses the last record entered.

Ex. WO1002421 has two entries. I want to keep the record of the last person Wilmer Tamayo-Sanchez.

This statement below is what I am using but as unfortunately, it still brings in duplicates.  
SELECT MAX(WO_NUMBER) as wo_number
	  ,FIRST_NAME
      ,LAST_NAME
  FROM BA_VIEW_WO_LABOR
WHERE SKILL NOT LIKE '%INSPECTOR'
GROUP BY WO_NUMBER
      ,FIRST_NAME
      ,LAST_NAME
	  having count(*)>1
ORDER BY WO_NUMBER 

Open in new window

Capture.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
SELECT wo_number, first_name, last_name
    FROM (SELECT wo_number,
                 first_name,
                 last_name,
                 ROW_NUMBER() OVER(PARTITION BY wo_number ORDER BY first_name, last_name) rn,
                 COUNT(*) OVER (PARTITION BY wo_number) cnt
            FROM ba_view_wo_labor
           WHERE skill NOT LIKE '%INSPECTOR')
   WHERE cnt > 1 AND rn = 1
ORDER BY wo_number
johnsoneSenior Oracle DBA

Commented:
How do you define "last one"?  Typically, there is a date field or something like that to order by to determine which one is first or last.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial