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

On Sun, Nov 13, 2005 at 21:12:07 +0100,
Guido Winkelmann <guido(at)unknownsite(dot)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