I have a table with 4 char error codes and the descriptions for them.
I have a table of sales with multiple error codes in a single string.
I designed a join using like which allows for the highest weighted error code to be joined on which works well except when 2 or more errors have the same weight as each other, which creates multiple joined rows.
IF the weights are equal, I just want 1 result (any will do)
Code | Description | Weight
'0041' | 'data error' | 4
'0019' | 'format error' | 2
'0099' | 'missing creator' | 2
ID | RespCode
1234 | '00410019'
1235 | '00990019'
ID | RespCode | Description
1234 | '00410019' | 'data error'
1235 | '00990019' | 'format error'
1235 | '00990019' | 'missing creator'
SELECT * FROM MyData
left JOIN ErrorCodes
LIKE '%' + ErrorCodes.Code + '%'
AND ErrorCodes.Weight = (
SELECT MAX (Weight) FROM ErrorCodes
WHERE MyDate.RespCode LIKE '%' + ErrorCodes.Code + '%'
SELECT ID, RespCode, Description FROM ( SELECT ID, RespCode, Description, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Weight DESC) as rown FROM MyData LEFT JOIN ErrorCodes ON MyData.RespCode LIKE '%' + ErrorCodes.Code + '%' ) a WHERE rown = 1
Need a recent version of SQLS (2008+) for this. You take your query with all the duplicated row ids, and you ask SQLserver to do an in-place aggregating (partition by) and row numbering; it will give a counter that counts 1, 2, 3, etc for each ID, the counter restarts upon a change of ID (thanks to partition by), and the rows it counts are ordered by weight descending (higher weight comes first
Then we just select only rows where the counter is 1
To see what I mean about the inner query, just highlight that one between the brackets and run it, to see the ROWN column and how it behaves
Note: I see that Shawn's query is more or less same (Sorry Shawn, only looked closely at your query after I wrote mine, because the windowing function was off screen due to long lines), only has an extra unneeded subquery, and also his partiton/order by clause is wrong (it selects the lowest weighted weight thanks to ASC, you wanted highest weight to take precedence)