Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
 Posted - 2011-07-25 : 16:09:18
|
| [code]Col1 QID CType Date---------- ------ ---- ----------------------- NULL 43393 1231 2011-04-01 18:01:54.3176582540130 43393 1241 2011-04-02 18:01:54.3176582571890 43393 1251 2011-04-08 18:01:54.3176582571890 43393 1251 2011-04-06 18:01:54.3176582540130 43393 0952 2011-04-02 18:01:54.317I want to delete duplicate record which has same col1 value of same CTYpe where minimum date of the duplicate record.In the above sample data i want to delete the below record6582571890 43393 1251 2011-04-06 18:01:54.317[/code] |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-07-25 : 16:48:33
|
| Is it fair to say that you want to delete all duplicate rows except the one with the MAXimum Date? (in case there are more than 2 dupes).You should add a constraint (Primary Key or Unique) to your table so that duplicate rows are not allowed in the table.Be One with the OptimizerTG |
 |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-07-25 : 17:03:50
|
| This is the reqs.I want to delete the duplicate record that share a common group (Col1 | QID| Ctype). The catch is I want to keep the record with the maximum date in the duplicate record found.There are only 2 dupes exists when check with ctypes.6582571890 43393 1251 2011-04-08 18:01:54.3176582571890 43393 1251 2011-04-06 18:01:54.317 i need to delete the below record 6582571890 43393 1251 2011-04-06 18:01:54.317Let me know if i m not clear in explaination. |
|
|
|
suba.anand
Starting Member
8 Posts |
Posted - 2011-07-26 : 05:49:54
|
| with cte as (select row_number() over (partition by col1, ctype order by col1, ctype) as rowcnt, col1, qid, ctype, datefrom table_namedelete from cte where rowcnt > 1kindly check for syntax. |
|
|
|
|
|
|
query