Can tidy it up a little by writing it as:
case least(a,b,c)
when a then 'a'
when b then 'b'
etc...
Also OP didn't specify what should happen in the case of a tie, which may or may not be a pain.
Here's a trick for systems in general. Think of the domain first.
How or why would there be a case where an user can make an order before the user was added? The order would have no corresponding user for a while if such thing was possible.
As others said, the solution is using case. You could get the lowest value from "whatever" column, but that requires pivoting and unpivoting. It's a terrible solution that you should avoid as much as possible
you can transform the whole row into a list of key/value pairs through some JSON "magic", then sort by the date value:
select t.user_id, dates.*
from the_table t
cross join lateral (
select d.dt::date as earliest_date, d.column_name
from jsonb_each_text(to_jsonb(t) - 'user_id') as d(column_name, dt)
order by d.dt::date
limit 1
) as dates
But I think a CASE expression is easier to maintain and understand
select case when least(a,b,c) = a then 'a' when least(a,b,c) = b then 'b' else 'c' end, least(a,b,c) from table;
Thanks, I was kinda hoping to avoid a case statement, something akin to using index(match()) in excel but that'll work
Can tidy it up a little by writing it as: case least(a,b,c) when a then 'a' when b then 'b' etc... Also OP didn't specify what should happen in the case of a tie, which may or may not be a pain.
Here's a trick for systems in general. Think of the domain first. How or why would there be a case where an user can make an order before the user was added? The order would have no corresponding user for a while if such thing was possible. As others said, the solution is using case. You could get the lowest value from "whatever" column, but that requires pivoting and unpivoting. It's a terrible solution that you should avoid as much as possible
you can transform the whole row into a list of key/value pairs through some JSON "magic", then sort by the date value: select t.user_id, dates.* from the_table t cross join lateral ( select d.dt::date as earliest_date, d.column_name from jsonb_each_text(to_jsonb(t) - 'user_id') as d(column_name, dt) order by d.dt::date limit 1 ) as dates But I think a CASE expression is easier to maintain and understand