mateoc15 mateoc15 - 3 months ago 8
SQL Question

MySQL logic for prioritizing matching pattern/text

I have a table of incidents that have a short_description. I'm trying to assign them to a category based on the text in that short_description (not ideal, I know, but I'm working with an existing system and don't have much control). So I created a lookup table with search_text to look for, and the category value that should be assigned to the incident. In some cases more than one search_text value matches the short_description. I want to use the priority field to choose the highest priority (lowest priority value, such as 1) when this happens. I feel like maybe this involves a window function or something, but I'm not sure how to approach it.

Can someone help me with the changes needed in the logic below?

Thanks!




The query below returns two results, but I want it to just return one (Cluster) because Cluster is priority 1, and Disk is priority 2. I only want one record per snumber (incident).

CREATE TABLE snow_incident_s_2 (
SNUMBER varchar(40) DEFAULT NULL,
SHORT_DESCRIPTION varchar(200) DEFAULT NULL
);

INSERT INTO snow_incident_s_2 (snumber, short_description) values ('INC15535802','Prognosis::ADMINCLUSTER [5251]::CmaDiskPartitionNearFull, PROGNOSIS:ADMINCLUSTER');

CREATE TABLE lkp_incident_category_2 (
incident_category_id smallint(6) NOT NULL AUTO_INCREMENT,
incident_type varchar(10) DEFAULT NULL,
category varchar(100) NOT NULL,
search_text varchar(200) NOT NULL,
priority smallint(6) DEFAULT NULL,
PRIMARY KEY (incident_category_id)
);

INSERT INTO lkp_incident_category_2 (incident_type, category, search_text, priority) values ('INC','Cluster','Cluster',1);
INSERT INTO lkp_incident_category_2 (incident_type, category, search_text, priority) values ('INC','Disk','Disk',2);

SELECT
inc.snumber,
inc.short_description,
ic.search_text,
ic.category
FROM
snow_incident_s_2 inc
LEFT JOIN
lkp_incident_category_2 ic ON inc.short_description LIKE CONCAT('%', ic.search_text, '%')
AND ic.incident_type = 'INC'

Answer

window functions don't exist in mysql. but you can mimic them using variables.

I have been trying to test this but sqlfiddle is not cooperating so here is what I believe should work for you give it a try.

SELECT *
FROM (
  SELECT 
      inc.snumber
      ,inc.short_description
      ,ic.search_text
      ,ic.category
      ,(@rownum := IF(@incnum=inc.snumber,@rownum+1,1)) as IncidentRowNum
      ,@incnum := inc.snumber
  FROM
      snow_incident_s_2 inc
      LEFT JOIN lkp_incident_category_2 ic
      ON inc.short_description LIKE CONCAT('%', ic.search_text, '%')
      AND ic.incident_type = 'INC'
      CROSS JOIN (SELECT @rownum := 0, @incnum := '') var
  ORDER BY
    ic.priority
) t
WHERE
 t.IncidentRowNum = 1
;

Fully tested and functional here is a sqlfiddle of it http://sqlfiddle.com/#!9/75ff1/7