Thread: Sorting by the maximum value of two columns

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?  Is there a function
for this purpose, or can I write my own, or do I need a different
approach entirely?

I hope what I'm trying to do is clear, but just in case I'll fake an
example.

column1    column2

4                  7
9                  5
1                  6
8                  2

MAX(4,7) is 7
MAX(9,5) is 9
MAX(1,6) is 6
MAX(8,2) is 8

Therefore, my SELECT should return
9                 5
8                 2
4                 7
1                 6

David

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