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,
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;
is this because first_value works only for numeric fields?
Advertisement
Answer
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.