Link to home
Get AccessLog in
Avatar of vhpcomp
vhpcomp

asked on

SQL Self-join to remove rows based on a date field

I need to group rows in a table and then keep the rows with the highest value in a date column:
In this example, rows would be grouped by name and source (the actual table has more fields to group by) and I want to delete all rows except 3.

ID  Name            Source    MyDate
1   Bob Smith       T1        1/1/2000
2   Bob Smith       T1            1/1/1999
3   Bob Smith       T1            10/10/2005
4   Bob Smith       T1            1/1/2004

The latest date may not be distinct for each grouping .  For the data below I could keep either row 3 or 5:

ID  Name            Source    MyDate
1   Bob Smith       T1        1/1/2000
2   Bob Smith       T1            1/1/1999
3   Bob Smith       T1            10/10/2005
4   Bob Smith       T1            1/1/2004
5   Bob Smith     T1                  10/10/2005

I assume I will need a top 1 somewhere in a subquery to accomplish this.  
I know this sould be simple but I've tried a number of queries with no success.
Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
once that is done:
DELETE t
FROM yourtable t
   WHERE t.ID < ( select Max(i.ID ) from yourtable i WHERE i.Source = t.Source and i.Name = t.Name )

Open in new window

Avatar of vhpcomp
vhpcomp

ASKER

This is exactly what I was looking for, thanks!