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 |
|
MrBloom
Starting Member
36 Posts |
 Posted - 2013-06-06 : 05:57:27
|
| I would like to find the rows where NAMEID, DATE, DESC is duplicated. which from the table below would select 2 rows for each NAMEID and from these I would like to delete the row where the value is shorter and has been rounded down. So I would like to delete these rows. 11 2001-11-13 P000721 1.244414272 2005-01-05 P000253 1.2881I am able to select the duplicate values but can't work out how to delete the row based on the length of the value. I assume where LEN() < 7 or something if the value is in varchar but I can't make it work. Any ideas. NAMEID DATE DESC Value11 2005-05-01 P000535 6.7447333838383311 2001-11-13 P000721 1.244411 2001-11-13 P000721 1.2444372286769914272 2005-01-05 P000253 1.2881112974334214272 2005-01-05 P000253 1.288114272 2004-06-23 P000342 4.55757595888858 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-06 : 06:07:19
|
| [code]--DELETE tSELECT *FROM(SELECT ROW_NUMBER() OVER(PARTITION BY NAMEID,[DATE],[DESC] ORDER BY LEN(Value) DESC) AS Seq,*)tWHERE Seq > 1[/code]first run the select and once happy uncomment delete and run it after removing select------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
MrBloom
Starting Member
36 Posts |
Posted - 2013-06-07 : 07:10:42
|
| thanks very much this works. ROW_NUMBER is a useful function. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
delete from a duplicate selection based on length