Search code examples
sqlimpala

Sort/Order string output Apache Impala SQL query

Hello I have a string output in my query and I want to sort by this column. The Column contains only strings but two of them are date strings and one is characters only.

I want to sort/order by the date strings first and the character string should be at last position.

SELECT column1, column2, column3 FROM table1 ORDER BY column1;

column1 contains following data as string:

ROW1: Older
ROW2: 19.03.2023
ROW3: 20.03.2023

I want the output as follows:

Column1        column2        column3
20.03.2023     some value     some value
19.03.2023     some value     some value
Older          some value     some value

How to do that, a simple ORDER BY is not working correctly. Thanks

Solution

  • You can split the data into yyyy,mm,dd, actual value, it should work. Pls check it out.

    order by split_part(column1,'.',3),  split_part(column1,'.',2),  split_part(column1,'.',1), column1