Thread: 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
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)--------------------------- > TIP 6: explain analyze is your friend
"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
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
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)
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!
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?
>> 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