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
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) |