asked
Get 1 row out of "almost" duplicate rows
Key datecolumn chkcol 112 2011-06-09 13:44:21:963 A 112 2011-06-09 13:44:21:963 B 114 2011-06-10 11:23:11:212 A 115 2011-06-09 09:12:32:576 A 116 2011-06-09 02:14:11:111 A 117 2011-06-03 12:32:29:218 C 117 2011-06-03 12:32:29:218 B 118 2011-06-02 10:10:03:432 C 116 2011-06-08 07:24:08:454 C 116 2011-06-08 07:24:08:454 D A has priority over B C has priority over B C has priority over D I want to create a view containing just 1 of each Key. I tried lowering the priority of the D’s and B’s by doing the following: WHERE t.datecolumn = ( SELECT MAX(CASE chkcol WHEN B THEN DATEADD(SECOND, -1, t2.datecolumn) WHEN D THEN DATEADD(SECOND, -1, t2.datecolumn) ELSE t2.datecolumn END ) FROM thetable t2 WHERE t2.key = t.key But this isn’t working :( Am I just brain dead today? Please help! THANK YOU!
You only want the key, date column and highest priority chkcol to be returned (where A>B>C>D)? If I got that right, you could do: ; with BaseData as ( SELECT Key,datecolumn,checkcol,ROW_NUMBER() over (partition by key,datecolumn order by checkcol) as db FROM [MyTable] ) select key,datecolumn,checkcol from BaseData where rn=1 If the priority has to be different, you can change the order by inside the row_number clause to reflect that(using a case statement). EDIT: re-reading your question, I think you want the prio to be A>C>B>D. If that is the case you can change the order by to be: CASE WHEN checkcol='A' THEN 1 WHEN checkcol='C' THEN 2 WHEN checkcol='B' THEN 3 WHEN checkcol='D' THEN 4 ELSE 5 -- didn't say if it was only going to be those 4 values END
sorry, forgot the from part (typing on a tablet device). I have changed my example to show the from inside the CTE. Place you original query inside the CTE replacing my example, but including the ROW_NUMBER column. Alter that to fit your priority section, then all should be fine. If not, come back here.
Thanks, William! I'm getting closer but I'm still seeing duplicates so I must be doing something wrong here: ; with BaseData as ( SELECT key, Datecolumn, chkcol, ROW_NUMBER() over (PARTITION by key, Datecolumn order by CASE chkcol WHEN 'A' THEN 1 WHEN 'C' THEN 2 WHEN 'B' THEN 3 ELSE 4 END) AS db FROM [MyTable] ) select key, datecolumn, chkcol FROM BaseData WHERE db = 1 ORDER BY key, datecolumn desc
