0

I've a table from SQL Server and I've managed to tidy up the data. How to make unselected time or delete the past time data. so just need early time. So I use this query:

SELECT table1.id, table1.name,MIN(time)
FROM table1 
GROUP BY id, name,time

and I'm failing when I try query like this:

DELETE FROM table1
WHERE table1.date EXCEPT MIN(time)

this is my table:

Id_student  name    date
1   azhar       06:19:20  
1   azhar       06:30:30  
1   azhar       06:39:30  
1   azhar       06:40:30  
1   azhar       06:49:30

and this is expected finished table:

Id_student  name    date
1   azhar   06:19:20

expected table

Dale K
  • 25,246
  • 15
  • 42
  • 71
mycode binary
  • 27
  • 1
  • 6
  • Does this answer your question? [Delete all records except the most recent one?](https://stackoverflow.com/questions/7238983/delete-all-records-except-the-most-recent-one) – Thom A Jul 20 '23 at 09:04
  • ya, any other same problem like this?...thanks alot.. i try to find the question like that... – mycode binary Jul 20 '23 at 09:08

1 Answers1

-1

You can first use your query that fetches the earliest time per id as subquery and then in a second step delete all other rows. Something like this:

WITH earliest AS
  (SELECT id, MIN(time) AS eTime
  FROM table1 
    GROUP BY id)
DELETE FROM table1 
  WHERE NOT EXISTS 
    (SELECT 1 FROM earliest WHERE id = table1.id AND table1.time = eTime);

See this fiddle. It shows this will work correctly.

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17