Ordering output rows by the maximum value of three virtual columns

From: Guido Winkelmann <guido(at)unknownsite(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Ordering output rows by the maximum value of three virtual columns
Date: 2005-11-13 20:12:07
Message-ID: 43779e1c$0$21955$9b4e6d93@newsread2.arcor-online.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message cjobbers 2005-11-13 20:58:06 Logging
Previous Message Bill Dika 2005-11-13 14:19:05 Re: Application using PostgreSQL as a back end (experienced programmers please)