question

sqltracy avatar image
sqltracy 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!
t-sqlviewdistinct
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
WilliamD avatar image
WilliamD answered
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
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqltracy avatar image sqltracy commented ·
Thank you! Where would my FROM clause be? This is actually like a 4 page long query . . . :-P
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
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.
0 Likes 0 ·
sqltracy avatar image sqltracy commented ·
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
0 Likes 0 ·
sqltracy avatar image sqltracy commented ·
Actually - that DID work! I had another issue with duplicate dates that I had to fix, but now it looks perfect. Thank you SO MUCH!!! :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.