How to Get the Min() of Two Fields in Postgres

How do I get the MIN() of two fields in Postgres?

LEAST(a, b):

The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details). NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

Note that GREATEST and LEAST are not in the SQL standard, but are a common extension. Some other databases make them return NULL if any argument is NULL, rather than only when all are NULL...

PostgreSQL Select the r.* by MIN() with group-by on two columns

I think you want this:

SELECT DISTINCT ON (user_id, activity_type_id, EXTRACT(year FROM start_date_local)) 
     *, EXTRACT(year FROM start_date_local) AS year
FROM results
ORDER BY user_id, activity_type_id, year, elapsed_time;

How to get min/max of two integers in Postgres/SQL?

Have a look at GREATEST and LEAST.

UPDATE my_table
SET my_column = GREATEST(my_column - 10, 0);

PostgreSQL: find minimum value across multiple columns but return column name

You can do:

select *,
  case when mp = alpha then 'col: alpha'
       when mp = bravo then 'col: bravo'
       when mp = charlie then 'col: charlie'
       when mp = delta then 'col: delta'
  end as lower_positive
from (
  select *,
    least(
      case when alpha > 0 then alpha end,
      case when bravo > 0 then bravo end,
      case when charlie > 0 then charlie end,
      case when delta > 0 then delta end
    ) as mp
  from t
) x

However, this solution doesn't account for multiple minimums; the first one (from left ro right) wins.

What's the best way to select the minimum value from several columns?

There are likely to be many ways to accomplish this. My suggestion is to use Case/When to do it. With 3 columns, it's not too bad.

Select Id,
       Case When Col1 < Col2 And Col1 < Col3 Then Col1
            When Col2 < Col1 And Col2 < Col3 Then Col2 
            Else Col3
            End As TheMin
From   YourTableNameHere

how to get min of more then one column in postregsql?

Option 1:

select CASE when a.min1 < a.min2 then a.min1 else a.min2 END as minimum,
CASE when a.max1 > a.max2 then a.max1 else a.max2 END as maximum
from (select min(col1) as min1, min(col2) as min2, max(col1) as max1, 
  max(col2) as max2 from myTable) a

Option 2:

select CASE when MIN(col1) < MIN(col2) THEN MIN(col1) ELSE MIN(col2) END as Minimum,
  CASE WHEN MAX(col1) > MAX(col2) THEN MAX(col1) ELSE MAX(col2) END as Maximum
from myTable

Final Option:

select LEAST(MIN(col1),MIN(col2)) as myMinimum, GREATEST(MAX(col1),MAX(col2)) as myMaximum from myTable

How about this one? :)

postgresql select minimum from a value pair of two columns

SELECT mk.* FROM meuk mk
JOIN ( SELECT zgroup, MIN(id) AS id
        FROM meuk
        GROUP BY zgroup
        ) agg ON agg.id = mk.id
        ;

Or this one:

SELECT * FROM meuk mk
WHERE NOT EXISTS ( SELECT  *
        FROM meuk nx
        WHERE nx.zgroup = mk.zgroup
        AND nx.id < mk.id
        )
        ;

PSQL determine the min value of date depending on another column

You can solve your problem with the window function ROW_NUMBER, that will assign a unique number to each of your rows. Since you want:

• only one row for each "ID", then you can use the PARTITION BY clause on "ID"
• the first "date", then you can use the ORDER BY clause on "date" ascendently

Once you get this ranking value, the rows to retrieve (for each "ID") will be the ones that have rank = 1 (the lowest "date" for each partition).

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER(
               PARTITION BY ID 
               ORDER     BY date
           ) AS rn
    FROM tab
)
SELECT ID, 
       pid,
       method, 
       date
FROM cte 
WHERE rn = 1 

Check the demo here.

---

Related Topics