I am doing a query where I group on the first 3 characters of FieldA and on one other field (FieldB). I would like then from each created group the longest matching substring, always starting from the left, in FieldA. The expectation is that at worst it provides the first 3 characters as they would need to match to be able to group.

I have contemplated doing a set of nested conditions where each level represents a subsequent character in FieldA.

The first issue I have with this is that the string length of FieldA is variable and more so length of the matching string will vary. I would need to place as many nested conditions as the maximum length of matching strings. Unless of course there is a dynamic way to do this piece.

The second issue is that I don’t know how to perpetuate a single character in FieldA only if all other rows in the grouping have the same character in the same position. I would best explain it as the character average but with zero variance ;p. If I could get some direction on how to do this last then I am confident I can do the nested conditions piece. Or at the very least I can truncate it to a number of characters that I’m comfortable with.

Test environment: http://sqlfiddle.com/#!9/5cd69e/2/0

Sample Data

+---------+--------+ | FieldA  | FieldB | +---------+--------+ | AABBCDE |      5 | | AABBFG  |      5 | | BABAAA  |      4 | | AABCCD  |      6 | | BABCAD  |      4 | | CDEFGH  |      1 | | CDFHGF  |      1 | +---------+--------+

Expected Results

+----------+--------+----------+--------------+ | grouping | FieldB | counting | LongestMatch | +----------+--------+----------+--------------+ | AAB      |      5 |        2 | AABB         | | AAB      |      6 |        1 | AABCCD       | | BAB      |      4 |        2 | BAB          | | CDE      |      1 |        1 | CDEFGH       | | CDF      |      1 |        1 | CDFHGF       | +----------+--------+----------+--------------+

Sample Data:

CREATE TABLE Table1     (`FieldA` varchar(7), `FieldB` int) ;  INSERT INTO Table1     (`FieldA`, `FieldB`) VALUES     ('AABBCDE', 5),     ('AABBFG', 5),     ('BABAAA', 4),     ('AABCCD', 6),     ('BABCAD', 4),     ('CDEFGH', 1),     ('CDFHGF', 1) ; 

Query:

SELECT   SUBSTR(`FieldA`,1,3) as grouping,   `FieldB`,   COUNT(`FieldA`) as counting FROM   Table1 GROUP BY   SUBSTR(`FieldA`,1,3),   `FieldB` ;