471,092 Members | 1,602 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Sorting based on maximum value over several columns

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 ma*******@postgresql.org

Nov 23 '05 #1
1 3247
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 sorter

But 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 ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by pmud | last post: by
19 posts views Thread by Owen T. Soroke | last post: by
reply views Thread by na | last post: by
5 posts views Thread by jrod11 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.