Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 76EC3632FB6 for ; Tue, 20 Apr 2010 14:02:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 54884-01 for ; Tue, 20 Apr 2010 17:02:12 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com [74.125.82.46]) by mail.postgresql.org (Postfix) with ESMTP id 6D065633269 for ; Tue, 20 Apr 2010 14:02:22 -0300 (ADT) Received: by wwb24 with SMTP id 24so3453504wwb.19 for ; Tue, 20 Apr 2010 10:02:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=fPSxtYAwOgHVqOH17CcRzwZBsYPe5p6z3pHdR1X0wIg=; b=BYDItfDSanzG/fQvh7rsnXOXvapKuKPdLCxwoztGU0oWN1JJX9axx4edTL13XBkiDs Mmk88juDuFZ0bSNSw1+waY1Gw7A022ppx55xUIKvcjtT9RaUd/n4wdvODAyDS4WquaUd 6Gy0SEzElrlkbAaxSu9zPa3NxX+4sBBJPBVas= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=E1xnbXdP7Nb2AMxm1y7E9LeWG/CxgwEawl0rIHi8KmxVAjBKX2/W4lenRomOaVmfEq 3Qab//G58CC8RSOertygkSvgGHIecIwd/b9qwSFBykcpBK7bAOrjMXbDFlKtEYNBC9lr GKgDDWG1RpchI6vVwUfkVbva+q6mpiXToGVwo= MIME-Version: 1.0 Received: by 10.216.183.71 with HTTP; Tue, 20 Apr 2010 10:02:21 -0700 (PDT) In-Reply-To: References: <2A8BBCA2-814C-4E8D-BCE1-11FA2F8D120E@gmail.com> Date: Tue, 20 Apr 2010 19:02:21 +0200 Received: by 10.216.86.3 with SMTP id v3mr4924157wee.190.1271782941139; Tue, 20 Apr 2010 10:02:21 -0700 (PDT) Message-ID: Subject: Re: How to max() make null as biggest value? From: Pavel Stehule To: Feixiong Li Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.097 tagged_above=-10 required=5 tests=AWL=-0.197, BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201004/74 X-Sequence-Number: 34001 2010/4/20 Pavel Stehule : > Hello > > 2010/4/14 Feixiong Li : >> Hi , guys , >> >> I am newbie for sql, I have a problem when using max() function, I need = get >> null when there are null in the value list, or return the largest value = as >> usual, who can do this? >> > > max() =C2=A0returns max value of some column > > create table foo(a int); > insert into foo values(10); > insert into foo values(33); > > postgres=3D# select * from foo; > =C2=A0a > ---- > =C2=A010 > =C2=A033 > (2 rows) > > Time: 0,524 ms > postgres=3D# select max(a) from foo; > =C2=A0max > ----- > =C2=A033 > (1 row) > > there is function greatest > > postgres=3D# select greatest(1,2,34,2,1); > =C2=A0greatest > ---------- > =C2=A0 =C2=A0 =C2=A0 34 > (1 row) sorry, greates_with_null postgres=3D# create or replace function greatest_strict(variadic anyarray) returns anyelement as $$ select null from unnest($1) g(v) where v is null union all select max(v) from unnest($1) g(v) limit 1 $$ language sql;CREATE FUNCTION Time: 232.528 ms postgres=3D# select greatest_strict(1,6); greatest_strict ----------------- 6 (1 row) Time: 3.094 ms postgres=3D# select greatest_strict(1,6, null); greatest_strict ----------------- (1 row) but you need PostgreSQL 8.4 > > regards > Pavel Stehule > >> i.e. =C2=A0max([1,2,3,4,5]) =3D> 5 >> max([1,2,3,4,5,null]) =3D> null >> >> thanks in advance! >> >> Feixiong >> feixiongli@gmail.com >> >> >> >> >> >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> >