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,242,037
  • 58
  • 646
  • 786
Hossein Nassaj
  • 3
  • 1
  • 1
  • 4

3 Answers3

6

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,242,037
  • 58
  • 646
  • 786
  • @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,889
  • 1
  • 23
  • 35
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
  • 146
  • 2
  • 6