0

Can you do somwthing like this in postresql?

select min(column1, column2) as Min, max(column1, column2) as Max from mytable;
helle
  • 11,183
  • 9
  • 56
  • 83
  • Do you want the minimum of column1 and column2 for each row on mytable, or the single minimum value across all rows? I assume the former. – Tom Jul 08 '10 at 12:53
  • This appears to be a duplicate of http://stackoverflow.com/questions/318988/ – Dave Johansen Nov 23 '11 at 18:03

3 Answers3

3

You want to use the LEAST(a, b) sql function.

This was answered on another stack overflow question How do I get the MIN() of two fields in Postgres?

SELECT LEAST(column1, column2) as Min,
       GREATEST(column1, column2) as Max
FROM mytable;

The official postgresql documentation is here.

Community
  • 1
  • 1
Tom
  • 4,742
  • 25
  • 32
2

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? :)

Fosco
  • 38,138
  • 7
  • 87
  • 101
  • Depending on your answer to Tom's comment on the main question, I'll have to make modifications. – Fosco Jul 08 '10 at 12:56
0
select 
    min(CASE when a.min1 < a.min2 then a.min1 else a.min2 END) as minimum, 
    max(CASE when a.max1 > a.max2 then a.max1 else a.max2 END) as maximum 
from myTable as a
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
  • 1
    you should put the `case with` outside `min()` to make sure of the index. – J-16 SDiZ Jul 08 '10 at 13:00
  • How does index affect here? There is no WHERE clause – Madhivanan Jul 08 '10 at 13:14
  • MIN can usually use index to return min; if you tell SQL to get the MIN(column_with_index). However, if you tell postgres to get MIN(CASE expression) it can not (?) use the fact that the case expression might be actually covered with index and it can not know that it is not dependent on each row, it has to actually evaluate case for each row and keep track of the minimum value. Try it and compare execution plans. – Unreason Jul 08 '10 at 13:22
  • Ok. Thanks. I will compare it – Madhivanan Jul 08 '10 at 14:12