Can you do somwthing like this in postresql?
select min(column1, column2) as Min, max(column1, column2) as Max from mytable;
views:
44answers:
3
+2
A:
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
2010-07-08 12:51:41
is there some easier way?
helle
2010-07-08 12:52:40
@helle: I added a second possibility..
Fosco
2010-07-08 12:55:13
Depending on your answer to Tom's comment on the main question, I'll have to make modifications.
Fosco
2010-07-08 12:56:14
Added LEAST and GREATEST
Fosco
2010-07-08 12:58:48
A:
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
2010-07-08 12:56:14
you should put the `case with` outside `min()` to make sure of the index.
J-16 SDiZ
2010-07-08 13:00:13
@Madhivanan, 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
2010-07-08 13:22:27
+2
A:
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.
Tom
2010-07-08 13:00:09