1

I have a table like this:

Category    Version
B   1.8.11
B   1.20.138
B   1.29.01
B   1.30.89
B   1.3.114

Now, I want to get the latest version. By default, order by doesn't work because 1.8.11 is treated the highest.

I understand one way is to use split_part and substring and get the latest version which in this case will be 1.30.89.

Just wanted to understand if there is a better way to achieve this.

Edit1:

I am using redshift postgresql.So, not all postgresql functions are available.

Another way I figured out yet is something like this. Herein I pad zeroes to the left and then I have to remove them before reporting the actual version.

(TO_CHAR(SPLIT_PART(version,'.',1),'fm000') + '.' + TO_CHAR(SPLIT_PART(version,'.',2),'fm000') + '.' + TO_CHAR((case when SPLIT_PART(version,'.',3) = '' then '0' else SPLIT_PART(version,'.',3) end),'fm000')) AS padded_version

EDIT2: I got a slightly better method. So, just sharing it here:

select category, version from
(SELECT CATEGORY,
             VERSION,
             ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY SPLIT_PART(VERSION,'.',1)::INT DESC,SPLIT_PART(VERSION,'.',2)::INT DESC),SPLIT_PART(VERSION,'.',3)::INT DESC)
from table1)
where row_number = 1

Thanks!

Isha Garg
  • 331
  • 1
  • 3
  • 12
  • I saw it answered on SO... – Vao Tsun Oct 06 '17 at 07:24
  • eg https://dba.stackexchange.com/a/74298/30035 – Vao Tsun Oct 06 '17 at 07:24
  • or https://stackoverflow.com/a/30773470/5315974 – Vao Tsun Oct 06 '17 at 07:25
  • or https://stackoverflow.com/a/43897730/5315974 – Vao Tsun Oct 06 '17 at 07:26
  • @VaoTsun Thanks for redirecting me to that question. I got to know something new. But unfortunately, I am using redshift's version of postgresql and it doesn't really support string_to_array operation. Is there any other workaround? – Isha Garg Oct 06 '17 at 09:30
  • ah redshiiift. then I suppose you way is good enough, o course it is limitedto n dots and so on. but I wont be able to help here, as I know nothing about redshift – Vao Tsun Oct 06 '17 at 09:34
  • This is NOT a duplicate question. please un-mark it as one – Jon Scott Oct 06 '17 at 11:54
  • You could use a python UDF to normalise the string into something that is more sortable. that approach gives you a bit more control than your current method (if you need it) – Jon Scott Oct 06 '17 at 11:56
  • Thanks for the suggestion @JonScott. For the time being I could do it using the split_part as mentioned in Edit2. But I will try python UPF as well. :) – Isha Garg Oct 10 '17 at 09:52

0 Answers0