Karissa Martin Karissa Martin -4 years ago 65
SQL Question

SQL join on partial strings yields too many records with max in subquery

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)

TABLE ErrorCodes

Code | Description | Weight
'0041' | 'data error' | 4
'0019' | 'format error' | 2
'0099' | 'missing creator' | 2


TABLE MyData

ID | RespCode
1234 | '00410019'
1235 | '00990019'


Results I get

ID | RespCode | Description
1234 | '00410019' | 'data error'
1235 | '00990019' | 'format error'
1235 | '00990019' | 'missing creator'


I only want 1 result for 1235 but even with Max or Top 1 in sub-select, I get two. How can I limit to 1 joined row?

SELECT * FROM MyData
left JOIN ErrorCodes
ON MyData.RespCode
LIKE '%' + ErrorCodes.Code + '%'
AND ErrorCodes.Weight = (
SELECT MAX (Weight) FROM ErrorCodes
WHERE MyDate.RespCode LIKE '%' + ErrorCodes.Code + '%'
)

Answer Source
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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download