Ordering output rows by the maximum value of three virtual columns - Mailing list pgsql-novice

Hi,

I'm looking for a way to sort the output rows of a SELECT expressions by the
maximum of three virtual columns of the output. Sorting it by one virtual
column seems to be no problem:

SELECT
  (<some subselect expression>) AS a,
  <some more columns>
  FROM <table>
  ORDER BY a;

works fine.

Now, I have three different subselects, all of them positive integers, and
I'd like the rows to be sorted by the maximimum of these three columns.
I tried

SELECT
  (<some subselect expression>) AS a,
  (<another subselect expression>) AS b,
  (<a third subselect expression>) AS c,
  <some more columns>
  FROM <table>
  ORDER BY
    CASE
      WHEN a >
        CASE
          WHEN
            b>c THEN b
          ELSE c
        END
      THEN a
      ELSE
        CASE
          WHEN
            b>c THEN b
          ELSE c
        END
    END;

but that'll tell me "ERROR:  column "a" does not exist".

The following:

SELECT
  (<first subselect expression>) AS a,
  (<second subselect expression>) AS b,
  (<third subselect expression>) AS c,
  CASE
    WHEN (<first subselect expression>) >
      CASE
        WHEN
          (<second subselect expression>)>(<third subselect expression>)
        THEN (<second subselect expression>)
        ELSE (<third subselect expression>)
      END
    THEN (<first subselect expression>)
    ELSE
      CASE
        WHEN
          (<second subselect expression>)>(<third subselect expression>)
        THEN (<second subselect expression>)
        ELSE (<third subselect expression>)
      END
  END AS last_changed
  <some more columns>
  FROM <table>
  ORDER BY last_changed;

works, but is very, very unelegant and takes a long time to execute even on
a small table. I suspect there are more elegant and faster ways to this.

So, how can this be done better?

        Guido