Hi Greg,
Although it doesn't really seem to be a very well-structured database design, I think there is a solution.
If the amount of fields is low, you can just stick a CASE in the MAX like:
SELECT ...,
MAX(
CASE WHEN value1 > value2 THEN
(CASE WHEN value1 > value3 THEN value1 ELSE value3 END)
ELSE
(CASE WHEN value2 > value3 THEN value2 ELSE value3 END)
END
) as sorter
FROM yourtable
GROUP BY ...
ORDER BY sorterBut if there are more than three values it will be a very long CASE-statement and you're probably better off defining a FUNCTION. I'm not sure whether it is possible to define a function with an unspecified amount of inputvalues, but you can also use the table type as input type and work with a table record in your function.
It might yield best performance, though, to create a C-function for this.
If there is already a "max of several fields"-function in PostgreSQL, than you can use that of course.
Best regards,
Arjen
On 7-11-2004 1:31, Net Virtual Mailing Lists wrote:
Hello,
Lets say I have data like this:
value1|value2|value3|value4|....|value(N) ------|------|------|------|----|-------- 100 | 200 | 300 | 400 | | 10 | 20 | | 40 | | | 15 | | 16 | | 5 | | | | |
Now I want to sort these based on the maximum value of the data in each row, so for sorting purposes I would have this:
sort ---- 400 40 16 5
Any ideas?... I've tried several things but none of them have given me the result I am after....
Thanks as always!
- Greg
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
