From: | Arjen van der Meijden <acmmailing(at)vulcanus(dot)its(dot)tudelft(dot)nl> |
---|---|
To: | Net Virtual Mailing Lists <mailinglists(at)net-virtual(dot)com> |
Cc: | Pgsql General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Sorting based on maximum value over several columns |
Date: | 2004-11-07 09:19:33 |
Message-ID: | 418DE8A5.5030403@vulcanus.its.tudelft.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 majordomo(at)postgresql(dot)org
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jerry III | 2004-11-07 09:29:30 | Re: Can this be indexed? |
Previous Message | Gaetano Mendola | 2004-11-07 08:48:45 | Re: Postresql RFD version 2.0 Help Wanted. |