SQL Statement

scf

假設我有個 table 有 3 個 field ([Name] nvarchar, [Last Update Date] DateTime, [ID] Auto), 咁如果我淨係想留最新 record, 其他 delete, 一條 sql statement 可以點做呢 ?

Example:
Chan        2008/10/16 0:00:00        1
Chan        2008/10/15 0:00:00        2
Chan        2008/10/01 0:00:00        3
Lee        2008/10/01 0:00:00        4
Lee        2008/10/07 0:00:00        5
Lam        2008/09/06 0:00:00        6
Lam        2008/06/01 0:00:00        7
要變成
Chan        2008-10-16 00:00:00.000
Lam        2008-09-06 00:00:00.000
Lee        2008-10-07 00:00:00.000
其他 delete ?

這樣有甚麼問題
Delete [TestTable].* From [TestTable] Left Join
(Select [Name] As [Name1], Max([Last Update Date]) As [MaxDate]
From [TestTable] Group By [Name])
On [TestTable].[Name] = [Name1] And [TestTable].[Last Update Date] = [MaxDate]
Where [MaxDate] Is Null;

Thanks

hang409

delete         tmptable
from        tmptable a
where         id NOT IN (select name, max(date1) from test group by name) t on a.id = t.id

henrywho

If multiple records exists for the same [Name] with the same latest [Last Update Date], how to you want to handle it?  Keep all or one only?

rabbit82047

馮空諗既,

1. delete from table where id not in (
   
2.         select MIN(id) from table t
   
3.         inner join (select name, max(date) as date from table group by name) as tmp on t.name = tmp.name and t.date = tmp.date
   
4.         group by t.name, t.date
   
5. )

複製代碼

你條query睇落好似合理, 可能SYNTAX 有問題

不過反正有ID , 點唔用ID 做 COND

[ 本帖最後由 rabbit82047 於 2008-10-16 20:11 編輯 ]

kjky

你既然唔要 duplicate 既 record, 儲 data 果陣就應該做 checking, 而唔係入完先 delete!

scf

原帖由 kjky 於 2008-10-16 20:45 發表
你既然唔要 duplicate 既 record, 儲 data 果陣就應該做 checking, 而唔係入完先 delete!

其實係比人問起, 咁我想知答案
唔通我好似你個答案咁答咩

scf

原帖由 henrywho 於 2008-10-16 18:05 發表
If multiple records exists for the same [Name] with the same latest [Last Update Date], how to you want to handle it?  Keep all or one only?

冇諗過依個問題

scf

原帖由 hang409 於 2008-10-16 14:14 發表
delete         tmptable
from        tmptable a
where         id NOT IN (select name, max(date1) from test group by name) t on a.id = t.id

係唔係只要包含 temp query, 都要用這種方法
thanks

alphaau

原帖由 scf 於 2008-10-17 00:50 發表


其實係比人問起, 咁我想知答案
唔通我好似你個答案咁答咩

dedup 左個table 先做野

henrywho

Assuming that you will keep one record only, and you have Oracle 10g or above,

1. delete  from table1
   
2. where   rowid in
   
3.         (
   
4.         select  rrowid
   
5.         from    (
   
6.                 select  rowid rrowid,
   
7.                         last_value(rowid) over (
   
8.                           partition by name
   
9.                           order by last_upd_dt, id
   
10.                           rows between unbounded preceding
    
11.                             and unbounded following
    
12.                           ) max_rrowid
    
13.                 from    table1
    
14.                 )
    
15.         where   rrowid <> max_rrowid
    
16.         )
    
17. /

複製代碼

or

1. delete  from table1
   
2. where   rowid not in
   
3.         (
   
4.         select  rrowid
   
5.         from    (
   
6.                 select  rowid rrowid,
   
7.                         last_value(rowid) over (
   
8.                           partition by name
   
9.                           order by last_upd_dt, id
   
10.                           rows between unbounded preceding
    
11.                             and unbounded following
    
12.                           ) max_rrowid
    
13.                 from    table1
    
14.                 )
    
15.         where   rrowid = max_rrowid
    
16.         )
    
17. /

複製代碼

I am not sure which one is faster in your environment.