Search code examples
mysqlsqlwindow-functions

MySQL first_value on varchar column returns records in wrong format

first_value function in MySQL returns varchar column in unexpected format.

I got a table 'test' with two columns

create table test (col1 varchar(10), col2 integer);

and has records like this,

enter image description here

when I run first_value function I get records like this

select *, first_value(col1) over(partition by col1 order by col2 desc) as max_col1 
from test;

enter image description here

is this because first_value works only for numeric fields?

Solution

  • is this because first_value works only for numeric fields?

    No, first_value() works as well for the varchar data type.
    The results you get are correct but in hexadecimal format!
    0x6B657931 is key1
    0x6B657932 is key2
    0x6B657933 is key3
    So this could be a collation issue or a problem with the software you are using.
    If you are using a fiddle-like site, then it is not strange that the results come as they are.
    Anyway, you can find more here: https://dev.mysql.com/doc/refman/8.0/en/charset-syntax.html
    If the problem persists, you can always use the unhex() function:

    select unhex('6B657931')

    will return:

    key1

    Or:

    select CAST(0x6B657931 AS CHAR)

    will also return:

    key1

    See the demo.