Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

2007-04-10 Thread John D. Burger 

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



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

2007-04-10 Thread Lorenzo Thurman 

Tom Lane wrote:

"Dann Corbit" <[EMAIL PROTECTED]> writes:

[EMAIL PROTECTED] 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?

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

2007-04-10 Thread Lorenzo Thurman 

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)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

2007-04-10 Thread Lorenzo Thurman 

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!

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

2007-04-09 Thread Brent Wood 

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

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

2007-04-09 Thread Merlin Moncure 

On 4/9/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Dann Corbit" <[EMAIL PROTECTED]> writes:
>> [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

2007-04-09 Thread Tom Lane 
"Dann Corbit" <[EMAIL PROTECTED]> writes:
>> [EMAIL PROTECTED] 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

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

2007-04-09 Thread Dann Corbit 
Just do a union and return the min

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] 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)---
> TIP 6: explain analyze is your friend

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster