http://www.ruslansivak.com This is the development blog. en-us Tue, 07 Sep 2010 22:52:06 -0400 Wed, 10 Jan 2007 14:06:00 -0400 BlogCFC http://blogs.law.harvard.edu/tech/rss russ@ruslansivak.com russ@ruslansivak.com russ@ruslansivak.com no http://www.ruslansivak.com/2007/1/10/Deleting-duplicate-rows-from-SQL-Server Lets say you have a table with duplicate data in it, and you'd like to get rid of it easily. For simplicity's sake, lets say you have a table named names and it contains two columns, nameid and firstname. <code> select top 10 * from names order by firstName </code> <code> nameId firstName ----------- ----------- 77 AARON 3920 AARON 2802 ABBEY 2260 ABBIE 1913 ABBY 831 ABDUL 854 ABE 485 ABEL 1739 ABIGAIL 347 ABRAHAM </code> You can see right off the bat, that you have 2 Aaron's. Lets find out how many duplicate names you have total. <code> select firstName from names group by firstName having count(firstName)>1 order by firstName </code> <code> firstName ----------- AARON ADAM ADRIAN ALBERT ALEX ALEXANDER ALEXIS ALI ALLEN ALVA ANDRE ANDREA ANDREW ANGEL ANGELO ANTHONY ANTONIA ANTONIO ARIEL ARTHUR ASHLEY AUBREY AUGUSTINE AUSTIN AVERY BENNIE BERNIE BERRY BILLIE BILLY BLAIR BLAKE BOBBIE BOBBY BRANDON BRETT BRIAN BRITT CAMERON CAREY CARL CARLOS CARMEN CAROL CARROL CARROLL CARY CASEY CECIL CHANG CHARLES CHARLIE CHI CHONG CHRIS CHRISTIAN CHRISTOPHER CHUNG CLAIR CLARENCE CLAUDE CLEO CLYDE CODY COLBY CONNIE COREY CORY COURTNEY CRUZ CURTIS DALE DALLAS DAN DANA DANIEL DANNIE DARNELL DARYL DAVID DEAN DEE DEMETRIUS DENNIS DENNY DEON DEVIN DEVON DION DOMINIQUE DONALD DONG DONNIE DORIAN DREW DUSTY EDDIE EDWARD ELISHA ELLIS ELMER ERIC ERIN EUGENE EVAN FRANCES FRANCIS FRANCISCO FRANK FRANKIE FRED FREDDIE GABRIEL GAIL GALE GARY GAYLE GENE GEORGE GERALD GERRY GLENN GREGORY GUADALUPE HAROLD HENRY HOLLIS HONG IRA IVORY JACK JACKIE JAE JAIME JAME JAMES JAMEY JAMIE JAN JASON JAY JEAN JEFFREY JEREMY JERRY JESSE JESSIE JESUS JEWEL JEWELL JIMMIE JIMMY JOAN JODY JOE JOEL JOEY JOHN JOHNIE JOHNNIE JOHNNY JON JORDAN JOSE JOSEPH JOSHUA JUAN JUDE JULIAN JULIO JUSTIN KARL KASEY KEITH KELLEY KELLY KENDALL KENNETH KERRY KEVIN KIM KIRBY KRIS KYLE LACY LANE LARRY LAUREN LAURENCE LAVERN LAVERNE LAWRENCE LEE LEIGH LEO LEON LESLEY LESLIE LESTER LEWIS LINDSAY LINDSEY LOGAN LONNIE LOREN LOU LOUIE LOUIS LUIS LUPE LYNN MAN MARIA MARIO MARION MARK MARLIN MARSHALL MARTIN MARTY MARY MATTHEW MAURICE MELVIN MERLE MERRILL MICAH MICHAEL MICHAL MICHEAL MICHEL MICKEY MIKE MINH MITCHELL MORGAN NICKY NOEL NORMAN NUMBERS ODELL OLLIE OSCAR OTHA PARIS PAT PATRICIA PATRICK PAUL PERRY PETER QUINN RANDY RAY RAYMOND REFUGIO RENE RICHARD RICKIE ROBBIE ROBERT ROBERTO ROBIN RONALD RONNIE RORY ROSARIO ROY ROYCE RUDY RUSSELL RYAN SAM SAMMIE SAMMY SAMUEL SANDY SANG SANTOS SCOTT SCOTTIE SEAN SHANE SHANNON SHAUN SHAWN SHAYNE SHELBY SHIRLEY SIDNEY SOL SON STACEY STACY STEPHEN STEVEN STEVIE SUNG SYDNEY TAYLOR TERRELL TERRY THANH THEO THOMAS TIMOTHY TOBY TOMMIE TOMMY TONY TORY TRACEY TRACY TRAVIS TRINIDAD TRISTAN TROY TYLER VAL VALENTINE VAN VERNON VICTOR VIRGIL WALTER WESLEY WHITNEY WILLIAM WILLIE YONG YOUNG (331 row(s) affected) </code> Turns out we have 331 duplicate names. Now, how do we get the Id of only 1 of the names? <code> select max(nameId) as nameId, firstName from names group by firstName having count(firstName)>1 order by firstName </code> <code> nameId firstName ----------- ----------- 3920 AARON 5053 ADAM 2178 ADRIAN 4673 ALBERT 3879 ALEX 3389 ALEXANDER 1676 ALEXIS 3653 ALI 4267 ALLEN 2391 ALVA 3744 ANDRE 1300 ANDREA 3422 ANDREW 1643 ANGEL 4265 ANGELO 3349 ANTHONY 1645 ANTONIA 3368 ANTONIO 2332 ARIEL 3916 ARTHUR 1282 ASHLEY 2284 AUBREY 3165 AUGUSTINE 5400 AUSTIN 4446 AVERY 2282 BENNIE 4107 BERNIE 4557 BERRY 1497 BILLIE 2700 BILLY 2575 BLAIR 5397 BLAKE 1507 BOBBIE 2238 BOBBY 3983 BRANDON 4361 BRETT 4801 BRIAN 3328 BRITT 3081 CAMERON 2134 CAREY 3366 CARL 4614 CARLOS 1330 CARMEN 1237 CAROL 2652 CARROL 2491 CARROLL 2713 CARY 1680 CASEY 2557 CECIL 4555 CHANG 2473 CHARLES 2412 CHARLIE 3387 CHI 2291 CHONG 1747 CHRIS 2122 CHRISTIAN 3187 CHRISTOPHER 3417 CHUNG 3161 CLAIR 5120 CLARENCE 4953 CLAUDE 1815 CLEO 3279 CLYDE 3592 CODY 5300 COLBY 1325 CONNIE 2598 COREY 2748 CORY 1464 COURTNEY 2405 CRUZ 5297 CURTIS 1915 DALE 3386 DALLAS 5036 DAN 1398 DANA 2540 DANIEL 4551 DANNIE 3562 DARNELL 3589 DARYL 2488 DAVID 2920 DEAN 1917 DEE 4354 DEMETRIUS 3620 DENNIS 5386 DENNY 4548 DEON 2825 DEVIN 2373 DEVON 5290 DION 1891 DOMINIQUE 3873 DONALD 5112 DONG 2614 DONNIE 3384 DORIAN 4492 DREW 3301 DUSTY 2076 EDDIE 4053 EDWARD 2242 ELISHA 5379 ELLIS 5032 ELMER 3644 ERIC 1379 ERIN 4212 EUGENE 4211 EVAN 1266 FRANCES 1612 FRANCIS 3943 FRANCISCO 3910 FRANK 1789 FRANKIE 5031 FRED 2323 FREDDIE 3254 GABRIEL 1374 GAIL 1909 GALE 3760 GARY 1594 GAYLE 2518 GENE 2535 GEORGE 4784 GERALD 2562 GERRY 4942 GLENN 4489 GREGORY 1523 GUADALUPE 4727 HAROLD 3558 HENRY 3537 HOLLIS 2846 HONG 2754 IRA 2604 IVORY 4436 JACK 1454 JACKIE 4168 JAE 1717 JAIME 5185 JAME 2094 JAMES 4126 JAMEY 1365 JAMIE 1559 JAN 3731 JASON 3345 JAY 1277 JEAN 4862 JEFFREY 5276 JEREMY 2057 JERRY 2263 JESSE 1434 JESSIE 3500 JESUS 1741 JEWEL 1803 JEWELL 1886 JIMMIE 4012 JIMMY 1281 JOAN 1621 JODY 2517 JOE 3867 JOEL 3114 JOEY 2038 JOHN 3158 JOHNIE 1610 JOHNNIE 3344 JOHNNY 4480 JON 2011 JORDAN 2927 JOSE 2581 JOSEPH 4860 JOSHUA 3077 JUAN 4541 JUDE 4011 JULIAN 5367 JULIO 4010 JUSTIN 4089 KARL 2066 KASEY 5018 KEITH 1669 KELLEY 1286 KELLY 2733 KENDALL 3803 KENNETH 1671 KERRY 3381 KEVIN 1337 KIM 3938 KIRBY 2052 KRIS 2807 KYLE 2151 LACY 3864 LANE 4381 LARRY 1381 LAUREN 4848 LAURENCE 2529 LAVERN 1674 LAVERNE 4711 LAWRENCE 1560 LEE 1658 LEIGH 4845 LEO 4589 LEON 1831 LESLEY 1362 LESLIE 5447 LESTER 5355 LEWIS 1521 LINDSAY 1520 LINDSEY 4772 LOGAN 2555 LONNIE 2504 LOREN 1823 LOU 4041 LOUIE 2550 LOUIS 4000 LUIS 1821 LUPE 1385 LYNN 4526 MAN 1226 MARIA 3376 MARIO 1397 MARION 2767 MARK 5007 MARLIN 5438 MARSHALL 4240 MARTIN 2716 MARTY 1220 MARY 3935 MATTHEW 3965 MAURICE 4287 MELVIN 2058 MERLE 4523 MERRILL 3632 MICAH 2018 MICHAEL 3964 MICHAL 3994 MICHEAL 3441 MICHEL 2689 MICKEY 5160 MIKE 3932 MINH 4329 MITCHELL 1795 MORGAN 3664 NICKY 2452 NOEL 3601 NORMAN 3335 NUMBERS 3129 ODELL 1716 OLLIE 5339 OSCAR 4283 OTHA 3225 PARIS 1606 PAT 1221 PATRICIA 3266 PATRICK 3013 PAUL 3928 PERRY 5250 PETER 4910 QUINN 2914 RANDY 2567 RAY 4282 RAYMOND 3750 REFUGIO 1893 RENE 2792 RICHARD 4906 RICKIE 1872 ROBBIE 2215 ROBERT 4687 ROBERTO 1319 ROBIN 3961 RONALD 2730 RONNIE 5245 RORY 1817 ROSARIO 4989 ROY 4419 ROYCE 4231 RUDY 5423 RUSSELL 2448 RYAN 3600 SAM 2502 SAMMIE 4571 SAMMY 5333 SAMUEL 1541 SANDY 3370 SANG 2831 SANTOS 3960 SCOTT 4683 SCOTTIE 3775 SEAN 3491 SHANE 1342 SHANNON 3889 SHAUN 1751 SHAWN 4984 SHAYNE 1738 SHELBY 1246 SHIRLEY 2702 SIDNEY 5236 SOL 3851 SON 1430 STACEY 1399 STACY 3819 STEPHEN 3886 STEVEN 3627 STEVIE 3488 SUNG 2230 SYDNEY 2024 TAYLOR 4818 TERRELL 1472 TERRY 3423 THANH 3816 THEO 3015 THOMAS 4561 TIMOTHY 2764 TOBY 1997 TOMMIE 2923 TOMMY 2763 TONY 4456 TORY 1499 TRACEY 1327 TRACY 5226 TRAVIS 2487 TRINIDAD 4031 TRISTAN 4367 TROY 3882 TYLER 3881 VAL 4454 VALENTINE 2935 VAN 5408 VERNON 4816 VICTOR 5405 VIRGIL 4143 WALTER 4674 WESLEY 1587 WHITNEY 2337 WILLIAM 1438 WILLIE 2307 YONG 2121 YOUNG (331 row(s) affected) </code> Now we can delete the record with the highest id. <code> delete from names where nameID IN (select max(nameId) as nameId from names group by firstName having count(firstName)>1) (331 row(s) affected) </code> If you wanted to delete the record with the lowest Id, you would run this code: <code> delete from names where nameID IN (select min(nameId) as nameId from names group by firstName having count(firstName)>1) (331 row(s) affected) </code> If you have more then 1 set of duplicates, you can just run the last piece of code a bunch of times until you have no more rows affected, or you can put it in a loop. SQL Server 2000 SQL Wed, 10 Jan 2007 14:06:00 -0400 http://www.ruslansivak.com/2007/1/10/Deleting-duplicate-rows-from-SQL-Server http://www.ruslansivak.com/2006/9/25/Using-SQL-2000s-Client-Network-Utility-to-create-aliases-in-Windows-x64 As we all know running SQL server on a non-standard port can help improve security. What makes the matter a little difficult is that on Windows x64, SQL Server 2000's Client Network Utility doesn't work quite as it should. Any aliases that you create in there will not be picked up by enterprise manager or any other tool that uses the libraries. In order to get around this problem, you need to use the client network utility from c:\windowsfolder\syswow64 (replace windowsfolder with the name of the folder where windows is installed, such as c:\winnt or c:\windows) You can also update the the Program menu's shortcut to point to cliconfg.exe that's located in C:\windowsfolder\SysWOW64\ Now it will make the proper registry entries and the programs that depend on it will work. Windows x64 SQL Server 2000 Mon, 25 Sep 2006 15:46:00 -0400 http://www.ruslansivak.com/2006/9/25/Using-SQL-2000s-Client-Network-Utility-to-create-aliases-in-Windows-x64