0

my data is:

ID  Name    date
1   Ben 2017-01-21
2   Mark    2017-01-20
3   Mark    2017-01-21
4   Ell 2017-01-19

and it should be

ID  Name    date
1   Ben 2017-01-21
3   Mark    2017-01-21
4   Ell 2017-01-19

just the older "mark" with ID 2 must be remove

Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
Hossein Nassaj
  • 3
  • 1
  • 4

3 Answers3

4

If you just want to return the most recent row for name, you can use:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.name = t.name);

In most databases, you can use similar logic for a delete:

delete from t
    where t.date < (select max(t2.date) from t t2 where t2.name = t.name)
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
  • @codewing . . . The `delete` statement is deleting all rows where the date for the name is less than the maximum date for the `name`. That appears to be the intent of the OP (if s/he really wants a `delete`). – Gordon Linoff Jan 21 '17 at 02:15
0

you can use following query. i usually avoid to add sub queries in select or where to avoid performance issues.

Select id, name date from mydata x
inner join (SELECT name,MAX(date) from mydata group by name) y on x.name=y.name
K D
  • 5,725
  • 1
  • 21
  • 33
0

It looks like the table has not been normalized. But as per the question following should work if the database is SQL Server 2008 and above, credit:

WITH cte AS (
      SELECT Id, Name, [Date]
         row_number() OVER(PARTITION BY Name ORDER BY [date]) AS [RowNum]
      FROM YourTable
    )
DELETE cte WHERE [RowNum] > 1
Community
  • 1
  • 1
Raishul
  • 136
  • 2
  • 6