Removing duplicate rows in SQL Server

If you are using SQL Server 2005 or greater (we need the CTE (Common Table Expression) functionality) you can in one statement delete duplicate rows from a table. You can look at all columns in the table or you can look at a subset of the columns. The example below uses just one column (EmailAddress), but you can replace that one column with a comma separated list of the columns you want to consider.

Before we go off and start running delete scripts on our data, it is always a good idea to make sure you have a backup of the table so you can check you work after or restore if something goes wrong.

Let's first look at the data

-- see what rows have duplicates and which ones don't
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person

-- show only the rows that are duplicates
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
select * FROM myTable
    WHERE   NumInstances > 1

The actual delete statement

-- do the actual deleting of the duplicate rows
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
DELETE  FROM myTable
    WHERE   NumInstances > 1

To modify the code to fit your situation you will typically just need to replace the items in red with the comma separated list of columns you want to consider. Next change the table name in blue to the table you are trying to remove the duplicates from. Finally, change the column in orange to be one or more columns (separated by comma). In this case, I have a primary key so, I used it. You could use all the columns you are considering as well.

For more info on how it works and pre-SQL 2005 solutions see here.

GEOTAB review

I recently had the pleasure of reviewing GEOTAB GPS System. They have given a lot of thought into making their platform accessible via their API and their Architecture is very nice. 

Recommendation / Summary

After reviewing their website in detail and attending a demo for GEOTAB, I would technically recommend this product.  The ability to integrate with their product is strong, scalable, and well designed. Their proprietary hardware has one of the lowest failure rates in the industry (or so they say) and provides additional information that adds value to the data. To get the best features, you should use their proprietary hardware. The system is extensible from a hardware and software perspective. You can readily integrate with their GEOTAB. 

Hardware

GEOTAB has proprietary hardware, but also works with third party hardware. Their proprietary hardware appears to be superior to the competition because it uses accelerometers as well as GPS. This allows it to accurately be able to identify drivers that hard brake at low speeds, identify drivers that are distracted, etc. It has a high compatibility with most every vehicle. Installation is very easy (3 minutes they say) to install the device by plugging it into the data port of the vehicle. The hardware can be extended to interface with additional sensors, driver identification via RFID, and Garmin devices. With the Garmin devices two communication be done.

Reports/Monitoring/Dashboards

They have 30 reports, dashboards and pride themselves on productivity monitoring / reporting. Most vendors appear to record data at a periodic interval, but they do not. The reason is that some key data can be missed. This is actually a big benefit in recording the accuracy of the data.

API

The API is available in C# and JavaScript, but can also be called by any language that can make a HTTP GET or POST request via HTTPS (and most any language can). The API appears to be robust and they have a web farm so it should be scalable as well. The API is well documented and has many examples. There is a data integration Windows application they provide the source code for that extracts data from GEOTAB. It could easily be extended to import the data into your databases. You should be able to use this API to integrate with GateHouse GPS Portal if you want to or store the data in a database or file system. You can programmatically add users, devices, etc via the API as well.

GEOTAB Software Extensibility

Their web application UI can be extended using JavaScript to add calls to your own systems, or tweak their pages. The pages are unique urls so linking to their pages should be relatively easy.

Email notifications can easily be customized by users, but notifications can also be sent as texts. Their UI can also be embedded in your systems as well.

MADP changes from 2013 to 2014 according to Gartner

In the world of MADP (Mobile Application Develop) things change very fast. In fact, in April 2012 Gartner said, "Designers have to make complex trade-offs between native, hybrid and Web-oriented mobile architectures. From 2003 through 2009, Gartner observed that the majority of high-value mobile applications were written as native, but that began to change around 2009 as techniques for wrapping Web technologies emerged. These techniques create hybrid mobile applications (wrapped applications, where the container is native code, but the experience leverages the WebView capability of the OS) or as mobile Web applications. In addition, some Web-oriented applications began to offer HTML5 features, such as advanced rendering and local storage, that previously were only available within the native style.

This caused a rapid shift of focus for many enterprises toward Web-oriented techniques — so much so that, based on our surveys conducted in 2011, 40% of enterprise application developers were still targeting native first for a variety of reasons (such as performance and disconnected mode). This migration will continue for two to three more years, and we predict that, by 2015, 80% of all mobile applications developed will be hybrid or mobile-Web-oriented."

After reading about MADPs, I decided it might be useful to visually see how the players have changed from April 2012 to August 2013. So, I took the Magic Quadrant charts from 2012 and 2013 and superimposed them into one chart, added arrows from 2012 to 2013 and consolidated the labels to produce the diagram below.

NOTE: The ones in red are new in 2013. The ones in gray disappeared in 2013 from the rankings.
Some observations:

Antenna - didn't see much change in score, but is a pretty strong Leader.
Kony and Adobe - are neck and neck and both moved from a strong Visionary to Leader.
Appcelerator - They have the strongest vision out of all the companies. They need a little work on executing before they will be a Leader though.
Salesforce.com - was Niche and has moved into a strong Challenger; nearly a leader with a tad bit more vision.
jQuery - moved from fairly weak Visionary to a Leader because of their improved Execution. They still seem to be a bit light on the vision though.
IBM - moved from Niche player to a strong Leader. They lead in second on both execution and vision. They could be a good choice overall.
SAP - stayed in the Leader circle, but has gained some execution, but at the expense of losing some vision.
Google - stayed in the Niche player square, but has significantly increased their vision.
Blackberry - stayed in Niche player square, and really just lost its ability to execute. Not much hope on this one I think.
Microsoft - stayed in the Niche player square, and gained a little vision, but at the expense of execution.
Apple - stayed in the Niche player square, and gained noticeable amount of vision, but again at the cost of execution.

I am very curious what the 2014 Gartner report will bring.

Thank you to all for your support

Just geeks has reached over 1 million hits since I started tracking hits. This is very exciting! Thank you all for visiting my blog.