Getting Rid of Partially Duplicate Rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Getting Rid of Partially Duplicate Rows

Author

Topic

gogetter
Starting Member

18 Posts

Posted - 2008-03-04 : 10:01:15

I have a staging table that contains employee records with associated data such as first name, last name, SSN, hire date payroll date etc. This data is going to be exported to a third party vendor so each employee record must be unique in order for them to import the data into their mainframe. What I need to do prior to exporting this data to text is keep only the employee record that contains the most recent payroll date. For example, say I have an employee who has been paid five times this year. I only need to send the row that is associated with their most recent payroll date. So how would I go about removing the other rows without creating another table? The trick is that all of these rows are actually unique due to the difference in payroll date so they are not actual duplicates. I was thinking along the lines of using a trigger but I wanted to check here and see if anyone has dealt with this type of problem before. Thanks in advance for any help you may be able to provide.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 10:09:04

Just take like this

SELECT OtherFields...,MAX(PayrollDate) AS Latest
FROM YourTable
GROUP BY OtherFields

gogetter
Starting Member

18 Posts

Posted - 2008-03-04 : 10:19:33

quote:
Originally posted by visakh16

Just take like this

SELECT OtherFields...,MAX(PayrollDate) AS Latest
FROM YourTable
GROUP BY OtherFields

Thanks for the response. I had actually tried the GROUP BY method with the MAX (payrolldate) but all rows still get returned in the result set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 10:36:04

Can you provide your table with some sample data?

gogetter
Starting Member

18 Posts

Posted - 2008-03-04 : 11:17:49

quote:
Originally posted by visakh16

Can you provide your table with some sample data?

Unfortunatley I can't due to the sensitivity of the data I am working with. I think I have figured out a solution, thanks for your responses.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-04 : 21:56:35

This will do it...


 SELECT y.*
   FROM YourTable y
  INNER JOIN
        (--==== Derived table "d" finds max payroll date for each SSN
         SELECT SSN, MAX(PayrollDate) AS MaxPayrollDate
           FROM YourTable
          GROUP BY SSN
        )d
     ON y.SSN = d.SSN
    AND y.PayrollDate = d.MaxPayrollDate

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources