2

I'm trying without success to solve this tricky point. I have a mysql table like the following:

Category      Model      Description
cat1           AAA       Triple A
cat1/cat2      AAA       Triple A
cat1/cat2      BBB       Triple B
cat1           BBB       Triple B
cat3           CCC       Triple C
cat3/cat4/cat5 CCC       Triple C
cat3/cat4      CCC       Triple C

etc.

I would like to select rows with unique Model and "longest" Category. Basically I would like to obtain:

Category      Model      Description
cat1/cat2      AAA       Triple A
cat1/cat2      BBB       Triple B
cat3/cat4/cat5 CCC       Triple C

Any hint?

Improve this question
3
  • 1
    If at all possible, you ought to split Category out into another table that links each category to a Model in a one-to-many relationship. You would then avoid this problem entirely. Commented Oct 3, 2012 at 15:17
  • I understand your point. Actually I have a situation similar to that one you are describing. But I need also the information of the deepest category path. Commented Oct 3, 2012 at 16:02
  • I see - I assumed that rather than paths, those were delimited category lists. Commented Oct 3, 2012 at 16:06

3 Answers 3

Reset to default
4 
select m.*
from MyTable m
inner join (
    select Model, max(char_length(Category)) as MaxLen
    from MyTable
    group by Model
) mm on m.Model = mm.Model and char_length(m.Category) = mm.MaxLen

You may in fact, want the deepest category rather than longest. If this is the case, there are better options than above.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

+1 How about a DISTINCT to dedupe those where there are multiple rows for a Category length, which if I'm not mistaken, would return in your outer select m.*?
you should change len() to length() for mysql
One more thing, I'd rather use CHAR_LENGTHinstead of LENGTH. Click here for better explanation by Andomar
Great, it worked perfectly... maybe this is the answer on the Gordon Linoff solution.
Sorry my comment is not clear maybe... I was just saying: maybe this is the answer to my question in Gordon Linoff solution.
|
3

From the data that you provide, the following will work:

select max(category), Model, Description
from t
group by Model, Description

This is because "cat1" < "cat1/cat2" and so on.

Improve this answer

3 Comments

+1 I wouldn't have thought of that. And based on the liberal requirement of "the longest string", it doesn't particularly matter if there are two equal length strings that contain different categories. MAX() will just pull the greater by string comparison.
Hmm, it seems a simple solution, I just have a minimal concern. Actually my real table has a lot of fields. Is there a way to select all of them?
Not the most robust solution—add a category named z and see what happens. This is basically the same as ORDER BY category DESC LIMIT 1.
1

If "longuest" means the maximum number of / in Category (as it should be), try this :

SELECT
    t.Category
    , t.Model
    , t.Description
FROM
    t
    INNER JOIN
    (
        SELECT
            Model
            , MAX(CHAR_LENGTH(Category) - CHAR_LENGTH(REPLACE(Category, '/', ''))) AS Depth
        FROM
            t
        GROUP BY
            Model
    ) AS tmax
        ON t.Model = tmax.Model
        AND CHAR_LENGTH(t.Category) - CHAR_LENGTH(REPLACE(t.Category, '/', '')) = tmax.Depth

Additionally, it seems to work : http://sqlfiddle.com/#!2/725e5/3/0

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.