Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A4FDC2E370E for ; Mon, 7 Jan 2008 19:16:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 22452-01 for ; Mon, 7 Jan 2008 19:16:19 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.177]) by postgresql.org (Postfix) with ESMTP id 073BF2E32EB for ; Mon, 7 Jan 2008 19:16:22 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so13187580waf.5 for ; Mon, 07 Jan 2008 15:16:21 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=w2+tsly6zlqeDOBkY6fAVVG5Bn19svSFi0OCprXtAtc=; b=bW2GnUTv4eFjYbgyTnrhCpbIWiaUsQczMaDVFhKpyoTZBlBbcSHJCSz+QQZRX5O50EYm3TB32M1rvvkvCQMfzf0r+nT4Tn9LXIa3yYmbFYc2UJrme0XwqXdRgmkBLtBglrxTTLi1ytfiHuEn94wngaCn/FORsnDZG2DGpfSLGZY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=mebQEB0kNI9BHNQkCyBJJSC02wa1ImCIMPEepWa4JvfDFBRkf2iZ/wAba8y3H9sy7UsYbxRxv1Ddg1nAPslPBjehBXsH3B0eMDKqhj8ep5kfOLDM8JoY3FjBNFXC+3biJwMzHONZvjP/05iCqJ9JvrWHWeUxh15JerjjIRKILZg= Received: by 10.114.80.4 with SMTP id d4mr10314wab.44.1199747781701; Mon, 07 Jan 2008 15:16:21 -0800 (PST) Received: by 10.114.131.4 with HTTP; Mon, 7 Jan 2008 15:16:21 -0800 (PST) Message-ID: Date: Mon, 7 Jan 2008 17:16:21 -0600 From: "Scott Marlowe" To: emilu@encs.concordia.ca Subject: Re: Get the max(value1, value2, value3) from a table Cc: "Josh Williams" , pgsql-sql@postgresql.org In-Reply-To: <4782AD66.3060606@encs.concordia.ca> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4782A1B2.5050804@encs.concordia.ca> <1199745537.5877.9.camel@godzilla.local.scalefeather.com> <4782AD66.3060606@encs.concordia.ca> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/42 X-Sequence-Number: 29895 On Jan 7, 2008 4:53 PM, Emi Lu wrote: > >>> select ?max?(col1, col2, col3) as result; > >>> will return > >>> > >>> result > >>> ------- > >>> 5 > >>> 8 > >>> 12 > >>> > >>> (3 rows) > >> 8.1 (I believe?) introduced GREATEST(), which does precisely what you're > >> looking for. > > > > How would greatest give him three rows like that? Maybe I'm > > misunderstanding what the OP was asking for... > > IF 8.1, "select greatest(col1, col2, col3) from test" is exactly what I > am looking for. > > I would do the optional query by union/or for now. OK, looking back at your example, I do think I got it wrong. The greatest thing should work... Here's a test from 8.1 to prove it ... create table test (col1 int, col2 int, col3 int); insert into test values (1,5,2); smarlowe=# insert into test values (8,1,3); smarlowe=# insert into test values (12,1,1); select greatest(col1,col2,col3) from test; greatest ---------- 5 8 12 tada! So yeah, you want 8.1 (or 8.2 or 8.3)