Обсуждение: max (timestamp,timestamp)
I tried the following query but the query fails as "function max (timestamp w. timezone,timestamp w. timezone) does not exist" SELECT id, MAX(last_updated, (SELECT MAX (last_updated) FROM product_category_member WHERE product_category_member.id = product_category.id)) FROM product_category product_category.last_updated and product_category_member.last_updated are timestamps with timezone. Is there any other way I can produce this result -- Regards, Tarlika Elisabeth Schmitz
am Mon, dem 13.11.2006, um 13:46:00 +0000 mailte T E Schmitz folgendes: > I tried the following query but the query fails as > "function max (timestamp w. timezone,timestamp w. timezone) does not exist" > > SELECT id, > > MAX(last_updated, > (SELECT MAX (last_updated) FROM product_category_member WHERE > product_category_member.id = product_category.id)) > > FROM product_category > > > product_category.last_updated and product_category_member.last_updated > are timestamps with timezone. Really, there are no such function. Perhaps this can help you: SELECT id, MAX(product_category.last_updated), MAX(product_category_member.last_updated) from product_category, product_category_member WHERE product_category_member.id = product_category.id; **untested** Your fault is that there are no max(timestamp,timestamp) - funktion and i think, you should read more about JOINs. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
max (timestamptz, timestamptz) does not exist already. You need to create a simple function in PLpgSQL something like if a > breturn a; elsereturn b; Even an sql function will do the job here using case statement. --Imad www.EntepriseDB.com On 11/13/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > am Mon, dem 13.11.2006, um 13:46:00 +0000 mailte T E Schmitz folgendes: > > I tried the following query but the query fails as > > "function max (timestamp w. timezone,timestamp w. timezone) does not exist" > > > > SELECT id, > > > > MAX(last_updated, > > (SELECT MAX (last_updated) FROM product_category_member WHERE > > product_category_member.id = product_category.id)) > > > > FROM product_category > > > > > > product_category.last_updated and product_category_member.last_updated > > are timestamps with timezone. > > Really, there are no such function. Perhaps this can help you: > > SELECT id, MAX(product_category.last_updated), > MAX(product_category_member.last_updated) from product_category, > product_category_member WHERE product_category_member.id = > product_category.id; > > **untested** > > > > Your fault is that there are no max(timestamp,timestamp) - funktion and > i think, you should read more about JOINs. > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
On Mon, Nov 13, 2006 at 07:29:09PM +0500, imad wrote: > max (timestamptz, timestamptz) does not exist already. You need to > create a simple function in PLpgSQL something like > > if a > b > return a; > else > return b; Since PostgreSQL 8.1 you can use GREATEST: test=> SELECT greatest(1, 2);greatest ---------- 2 (1 row) test=> SELECT greatest(2, 1);greatest ---------- 2 (1 row) test=> SELECT greatest(6, 3, 1, 10, 9, 5, 2, 7, 8, 4);greatest ---------- 10 (1 row) -- Michael Fuhr
Michael Fuhr wrote: > On Mon, Nov 13, 2006 at 07:29:09PM +0500, imad wrote: > >>max (timestamptz, timestamptz) does not exist already. You need to >>create a simple function in PLpgSQL something like >> >>if a > b >>return a; >>else >>return b; > > > Since PostgreSQL 8.1 you can use GREATEST: > > test=> SELECT greatest(1, 2); That'll be a handy feature! Unfortunately, my server is still on 7.4. Thanks to everyone for the quick responses. -- Regards, Tarlika