return old and latest date from a column in a table as a single column with two rows

Question

I'm working on postgresql database server. Can i get min and max date from a column in a single column? I have two tables,i want to get the min and max date stored in table1 cfrange column which is of type character varying. table1 and table2 is mapped using sid. table1:

sid  cfrange  

100   3390
101   8000
102   5/11/2010
103   11/12/2016
104   01/03/2016
105   4000
106   4000
107   03/12/2017
108   03/11/2016
109   4/04/2018
110   10/12/2016

table2:

sid  description

102  success
103  success
104  Proceeding
107  success
108  success

Please see the code below:

select min(cfrange::date) as min_cf,
max(cfrange::date) as max_cf from (select table1.sid, table1.cfrange::date as cfrange from table1 inner join table2 on table1.sid = table2.sid) tt;

I want the out put as below:

min_max
2010-11-05
2017-12-03

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2017-05-19T03:12:24

You could just union together two subqueries, one which finds the minimum and one which finds the max:

WITH cte AS (
   SELECT
       t1.sid,
       t1.cfrange::date AS cfrange
   FROM table1 t1
   INNER JOIN table2 t2
       ON t1.sid = t2.sid
)
SELECT MIN(t.cfrange::date) AS min_max
FROM cte t
UNION ALL
SELECT MAX(t.cfrange::date)
FROM cte t
ORDER BY min_max

If you want a single query which you could, for example, run directly from your Java code, then you can embed the CTE directly, e.g.

SELECT MIN(t.cfrange::date) AS min_max
FROM
(
   SELECT
       t1.sid,
       t1.cfrange::date AS cfrange
   FROM table1 t1
   INNER JOIN table2 t2
       ON t1.sid = t2.sid
) t
UNION ALL
SELECT MAX(t.cfrange::date)
FROM
(
   SELECT
       t1.sid,
       t1.cfrange::date AS cfrange
   FROM table1 t1
   INNER JOIN table2 t2
       ON t1.sid = t2.sid
) t
ORDER BY min_max

return old and latest date from a column in a table as a single column with two rows

1 Answers