sql server – Using t-sql to select a dataset with duplicate values removed – Education Career Blog

I have a set of tables in SQL Server 2005 which contain timeseries data. There is hence a datetime field and a set of values.

CREATE TABLE dbo.raw_data(
    Time datetime NULL,
    field1 float NULL,
    field2 float NULL,
    field3 float NULL
)

The datetime field is unfortunately not a unique key, and there appear to be a lot of datetime values with multiple (non-identical) entries – hence DISTINCT doesn’t work.

I want to select data from these tables for insertion into a new, properly indexed table.

Hence I want a select query that will return a dataset with a single row entry for each Time. I am not concerned which set of values is selected for a given time, as long as one (and only one) is chosen.

There are a LOT of these tables, so I do not have time to find and manually purge duplicate values, so a standard HAVING COUNT(*)>1 query is not applicable. There are also too many duplicates to just ignore those time values altogether.

Any ideas? I was thinking of some kind of cursor based on PARTITION BY, but got stuck beyond that point.

,

You don’t need a cursor:

SELECT tmp.*
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Time ORDER BY Time) AS RowNum
    FROM raw_data
) AS tmp
WHERE tmp.RowNum = 1

Leave a Comment