Select the closest before and after value.

Question

I have a table like this

let's say show_date table

different | date ---------------------- -2 | 2011-04-18 ---------------------- -1 | 2011-04-19 ---------------------- 4 | 2011-04-24 ---------------------- 5 | 2011-04-25 ---------------------- 

I want to select 2 values, the first is the first negative value, the second is the first positive value. in this example, -1 (2011-04-19) and 4 (2011-04-24) will be received

I am trying to arrange abs (other), but it gives 2 closest ones that are -1 and -2

Answer 1

 SELECT MIN(CASE WHEN different > 0 THEN different ELSE NULL END) AS MinPositive, MAX(CASE WHEN different < 0 THEN different ELSE NULL END) AS MaxNegative FROM show_date 

Answer 2

 (SELECT * FROM show_date WHERE different < 0 ORDER BY different DESC LIMIT 1) UNION ALL (SELECT * FROM show_date WHERE different >= 0 ORDER BY different LIMIT 1) 

Answer 3

 SELECT min (a.nr), max (b.nr) FROM demo a, demo b WHERE a.nr > 0 AND b.nr < 0; min | max -----+---- 1 | -1 (1 Row) 

Alternative approach - the above solution becomes problematic for large tables:

 SELECT min(date) FROM tbl WHERE date > 0 UNION SELECT max(date) FROM tbl WHERE date < 0;