Home Ignoring Duplicate Records SQL
 In need of some help :) So I have a table of records with the following columns: Key (PK, FK, int) DT (smalldatetime) Value (real)  The DT is a datetime for every half hour of the day with an associated value E.g. Key DT VALUE 1000 2010-01-01 08:00:00 80 1000 2010-01-01 08:30:00 75 1000 2010-01-01 09:00:00 100  I have a Query that finds the max value every 24 hour period and its associated time however, on one day the max value occurs twice and hence duplicates the date which is causing processing issues. I have tried using rownumber() which works but I can't use a calculated column in my where clause? Currently I have: SELECT cast(T1.DT as date) as 'Date',Cast(T1.DT as time(0)) as 'HH', ROW_NUMBER() over (PARTITION BY cast(DT as date) ORDER BY DT) AS 'RowNumber' FROM TABLE_1 AS T1 INNER JOIN ( SELECT CAST([DT] as date) as 'DATE' , MAX([VALUE]) as 'MAX_HH' FROM TABLE_1 WHERE DT > '6-nov-2016' and [KEY] = '1000' GROUP BY CAST([DT] as date) ) AS MAX_DT ON MAX_DT.[DATE] = CAST(T1.[DT] as date) AND T1.VALUE = MAX_DT.MAX_HH WHERE DT > '6-nov-2016' and [KEY] = '1000' ORDER BY DT  This results in Key DT VALUE HH 1000 2010-01-01 80 07:00:00 1000 2010-02-01 100 17:30:00 1000 2010-02-01 100 18:00:00  I need to remove the duplicate date (I Have no preference which HH it takes) I think I've explained that terribly, let me know if it makes no sense and i'll try and re write Any ideas?