PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to choose lowest number value from varchar record

How can i choose lowest number from this varchar column ?

Ranks
131,31,97,347,1
2,61
197,998,48,9
941,439,13

This is just example. I have table with around 12000 records and i need to choose lowest value from every record.
Table is varchar but values inside are numeric as you can see.

Tags:

2

Answers


  1. 0

    When you use postgresql, you can simply do it. I will provide sample code.

    select id, min(cast(unnset(string_to_array(ranks, ',')) as integer)) as lowest_value from your_table_name group by id;

    The goal of the above code is to remove , in varchar values and convert the value to integer and then choose lowest value of the converted values. I hope it will helps you.

    Login or Signup to reply.
  2. 0

    The simplest and most efficient approach for your case in PostgreSQL would be the one that uses unnest in combination with string_to_array.

    SELECT id, MIN(CAST(value AS INT)) AS lowest_value FROM your_table_name, unnest(string_to_array(ranks, ',')) AS value GROUP BY id;

    This query is straight forward, with just a few SQL functions (string_to_array, unnest, and MIN).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search