Search code examples
mysqlsqlmariadbrankpartition-by

MySQL Partition using column LIKE values

I've a below table in MySQL called "test".

data        value
ABC>1234    5678
ABC>2345    2435
ABC>3456    4567
DEF>4567    5656
DEF>5678    7535
DEF>6789    7983
GHI>7890    6748
GHI>8901    2973
GHI>8901    3024

Now I wish to partition the table based on first keyword till greater than symbol (>) in the data field and wish to get RANK().

In order to achieve this, I'm running below query.

SELECT *, RANK() OVER (PARTITION BY `data`) FROM `test`

After running the above query, I'm getting below output.

data        value   RANK()
ABC>1234    5678    1
ABC>2345    2435    1
ABC>3456    4567    1
DEF>4567    5656    1
DEF>5678    7535    1
DEF>6789    7983    1
GHI>7890    6748    1
GHI>8901    2973    1
GHI>8901    3024    2

But desired output should be as mentioned below.

data        value   RANK()
ABC>1234    5678    1
ABC>2345    2435    2
ABC>3456    4567    3
DEF>4567    5656    1
DEF>5678    7535    2
DEF>6789    7983    3
GHI>7890    6748    1
GHI>8901    2973    2
GHI>8901    3024    3

Kindly help to get the desired output.

Solution

  • Your sample data and output imply you want this query:

    SELECT *,
    RANK() OVER (PARTITION BY SUBSTRING_INDEX(data, '>', 1)
                 ORDER BY SUBSTRING_INDEX(data, '>', -1), value) rnk
FROM test;

    That is, the partition is defined by the first three characters of the data, and the rank order is determined by the final four digits, or, should there be a tie, the tie would be broken by the value.