Ordering output rows by the maximum value of three virtual columns
От | Guido Winkelmann |
---|---|
Тема | Ordering output rows by the maximum value of three virtual columns |
Дата | |
Msg-id | 43779e1c$0$21955$9b4e6d93@newsread2.arcor-online.net обсуждение исходный текст |
Ответы |
Re: Ordering output rows by the maximum value of three virtual columns
|
Список | 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
В списке pgsql-novice по дате отправления: