Lithodora Lithodora - 5 months ago 19
SQL Question

Select lowest value in a column from a set

I have a table in PostgreSQL that has information like this:

ID | Value | Total
12 | 'foo' | 16
15 | 'loo' | 1
13 | 'too' | 11
67 | 'roo' | 7


The query I have to build will be given either a single ID or multiple IDS which are comma separated. Only the Value needs be returned. If there are multiple IDs then return only the result of the set that has the lowest Total.

This is my start, but it isn't exactly what I am after:

IF(position(',' in sample_id)>0) THEN
RETURN QUERY SELECT value FROM table WHERE table.id = ANY(regexp_split_to_array(sample_id,','));
ELSE
RETURN QUERY SELECT value FROM table WHERE table.id = sample_id;
END IF;


EDIT: To be clear I am building a function that

CREATE OR REPLACE FUNCTION public.get_test_results(IN sample_id text)
RETURNS TABLE(test_result text) AS
$BODY$
BEGIN
IF(position(',' in sample_id)>0) THEN
RETURN QUERY SELECT value FROM table WHERE table.id = ANY(regexp_split_to_array(sample_id,','));
ELSE
RETURN QUERY SELECT value FROM table WHERE table.id = sample_id;
END IF;
END;
$BODY$


It is used:

get_test_results("342949283940829308")


OR

get_test_results("67, 12")


The ID values in this call do not correlate to the example table. The result should ONLY return the value from the lowest TOTAL of the IDs in the set if there is more than one ID passed to the function.

Answer

Just use a limit clause:

select value 
from the_table
where id = ANY(regexp_split_to_array(sample_id,',')::int[])
order by total 
limit 1;

Note that you also need to cast the result of regexp_split_to_array to an integer array (::int[]) otherwise you cannot use any() to compare that with an integer column.

You also don't need PL/pgSQL for this. A simple SQL function will do:

CREATE OR REPLACE FUNCTION public.get_test_results(IN sample_id text)
   RETURNS TABLE(test_result text) AS
$BODY$
  select value 
  from the_table
  where id = ANY(regexp_split_to_array(sample_id,',')::int[])
  order by total 
  limit 1;
$BODY$
language sql;

Note that if there are multiple rows with the same minimum value, the above will only return a single row.

If you need all those rows, you can use a window function:

select value
from (
  select value, dense_rank() over (order by total) as rnk
  from the_table
  where id = ANY(regexp_split_to_array(sample_id,',')::int[])
) t
where rnk = 1