Re: Sorting by the maximum value of two columns

On Tue, Oct 04, 2005 at 04:59:21PM -0400, David Gaudine wrote:
> I want to use SELECT to view some records sorted (ordered?) by the 
> maximum value of two fields.  I tried
> 
> SELECT * FROM mytable ORDER BY MAX(column1,column2)
> 
> but there's no such function.  How can I do this?

PostgreSQL 8.1 will have GREATEST and LEAST functions so you'll
be able to do this:

SELECT * FROM mytable ORDER BY GREATEST(column1, column2);

In earlier versions you can easily write your own:

CREATE FUNCTION mygreatest(anyelement, anyelement) RETURNS anyelement AS '
SELECT CASE WHEN $1 > $2 THEN $1 ELSE $2 END;
' LANGUAGE sql IMMUTABLE STRICT;

-- 
Michael Fuhr