CODEWITHSUNDEEP
mysqlduplicates
madimar
madimar

Reputation: 35

Select rows with a duplicate field based on another field

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?

Upvotes: 2

Views: 111

Answers (3)

psadac
psadac

Reputation: 2342

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 3

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171351

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.

Upvotes: 4

Related Questions