SQL Server Delete unecessary time from selected early of time [duplicate]

bjg7j2ky 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(109)

This question already has answers here:

Delete all records except the most recent one? (3 answers)
Closed 12 days ago.

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

sql-server

来源：https://stackoverflow.com/questions/76728251/delete-unecessary-time-from-selected-early-of-time

1条答案

按热度按时间

oipij1gg1#

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.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Delete unecessary time from selected early of time [duplicate]

1条答案

相关问题

热门标签

最新问答