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