NEWBIE: How do I get the oldest date contained in 3 tables

Lorenzo Thurman wrote:

I have three tables using date fields. I want to retrieve the oldest
date contained in the tables. Can someone show me an example of a query
that would do that?
TIA

I think I have it, but if anyone has any comments, I'd appreciate it:

select min(old) as oldest from (select distinct min(create) as old from tab1
UNION
select distinct min(time_now) as old from tab1
UNION
select distinct min(create_time) as old from tab2)

Just do a union and return the min

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Lorenzo Thurman
Sent: Monday, April 09, 2007 7:32 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] NEWBIE: How do I get the oldest date contained in 3
tables

I have three tables using date fields. I want to retrieve the oldest
date contained in the tables. Can someone show me an example of a

query

that would do that?
TIA

---------------------------(end of

broadcast)---------------------------

"Dann Corbit" <DCorbit@connx.com> writes:

owner@postgresql.org] On Behalf Of Lorenzo Thurman
I have three tables using date fields. I want to retrieve the oldest
date contained in the tables. Can someone show me an example of a query
that would do that?

Just do a union and return the min

That's probably not enough detail for a newbie ...

SELECT min(x) FROM
(
SELECT min(datecol1) AS x FROM table1
UNION ALL
SELECT min(datecol2) AS x FROM table2
UNION ALL
SELECT min(datecol3) AS x FROM table3
) ss;

Exercise for newbie: which of the AS clauses are redundant?

regards, tom lane

On 4/9/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Dann Corbit" <DCorbit@connx.com> writes:

owner@postgresql.org] On Behalf Of Lorenzo Thurman
I have three tables using date fields. I want to retrieve the oldest
date contained in the tables. Can someone show me an example of a query
that would do that?

Just do a union and return the min

That's probably not enough detail for a newbie ...

SELECT min(x) FROM
(
SELECT min(datecol1) AS x FROM table1
UNION ALL
SELECT min(datecol2) AS x FROM table2
UNION ALL
SELECT min(datecol3) AS x FROM table3
) ss;

Exercise for newbie: which of the AS clauses are redundant?

they are all unnecessary :D

select least
(
(SELECT min(datecol1) FROM table1),
(SELECT min(datecol2) FROM table2),
(SELECT min(datecol3) FROM table3)
);

merlin

Tom Lane wrote:

"Dann Corbit" <DCorbit@connx.com> writes:

owner@postgresql.org] On Behalf Of Lorenzo Thurman
I have three tables using date fields. I want to retrieve the oldest
date contained in the tables. Can someone show me an example of a query
that would do that?

Just do a union and return the min

That's probably not enough detail for a newbie ...

SELECT min(x) FROM
(
SELECT min(datecol1) AS x FROM table1
UNION ALL
SELECT min(datecol2) AS x FROM table2
UNION ALL
SELECT min(datecol3) AS x FROM table3
) ss;

Exercise for newbie: which of the AS clauses are redundant?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Um, all of them?

Lorenzo Thurman wrote:

I have three tables using date fields. I want to retrieve the oldest
date contained in the tables. Can someone show me an example of a
query that would do that?

You could write a custom function doing the same sort of thing, or
(perhaps more portable) use generic sqls & views like:

create view min_dates as
select min(date1) from table1as date_1
union
select min(date2) from table2 as date_1
union
select min(date3) from table3 as date_1;

then either:

select min(date_1) from min_dates;

or

create view min_date as
select min(date_1) as min_date
from min_dates;

then just:

select min_date from min_date;

Cheers

Brent Wood

Brent Wood wrote:

Lorenzo Thurman wrote:

I have three tables using date fields. I want to retrieve the oldest
date contained in the tables. Can someone show me an example of a
query that would do that?

You could write a custom function doing the same sort of thing, or
(perhaps more portable) use generic sqls & views like:

create view min_dates as
select min(date1) from table1as date_1
union
select min(date2) from table2 as date_1
union select min(date3) from table3 as date_1;

then either:

select min(date_1) from min_dates;

or

create view min_date as
select min(date_1) as min_date
from min_dates;

then just:

select min_date from min_date;

Cheers

Brent Wood

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thanks for the tip!

SELECT min(x) FROM
(
SELECT min(datecol1) AS x FROM table1
UNION ALL
SELECT min(datecol2) AS x FROM table2
UNION ALL
SELECT min(datecol3) AS x FROM table3
) ss;
Exercise for newbie: which of the AS clauses are redundant?

Um, all of them?

Yah, but only if you do this:

SELECT min(*) FROM
(
SELECT min(datecol1) FROM table1
UNION ALL
SELECT min(datecol2) FROM table2
UNION ALL
SELECT min(datecol3) FROM table3
) ss;

Otherwise you need the first one, I think, unless you want to rely on
PG's naming conventions for columns, then you could do:

select min(min) from
(
SELECT min(datecol1) FROM table1
UNION ALL
...
)

- John D. Burger
MITRE