Thread: Ordering output rows by the maximum value of three virtual columns

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

On Sun, Nov 13, 2005 at 21:12:07 +0100,
  Guido Winkelmann <guido@unknownsite.de> wrote:
> 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

In 8.1 there is a greatest function that you can use instead of the
case statements.

Your other issue has to do with what is allowed in order by. From the manual:
expression can be the name or ordinal number of an output column (SELECT list
item), or it can be an arbitrary expression formed from input-column values.

So you can't make expressions with output column names.

>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend