Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id C557D6362D4
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  2 Jul 2009 14:37:36 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 83050-01-4 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  2 Jul 2009 14:37:35 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.emproshunts.com (mail.emproshunts.com [66.64.191.19])
	by mail.postgresql.org (Postfix) with ESMTP id 6E76D6360FD
	for <pgsql-sql@postgresql.org>; Thu,  2 Jul 2009 14:37:34 -0300 (ADT)
Received: from [192.168.1.45] by mail.emproshunts.com (GMS
	15.02.3689/KX5895.00.ca608199) with ESMTP id paozkaaa for
	pgsql-sql@postgresql.org; Thu, 2 Jul 2009 13:38:27 -0400
Message-ID: <4A4CF058.6000704@emproshunts.com>
Date: Thu, 02 Jul 2009 13:37:28 -0400
From: justin <justin@emproshunts.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Create custom aggregate function and custom sfunc
References: <a0eee4d40907020748p7cb9bad7mad8817e60e9ea89@mail.gmail.com>
In-Reply-To: <a0eee4d40907020748p7cb9bad7mad8817e60e9ea89@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Originating-IP: [192.168.1.45]
X-DNSBL: 0
X-AntiSpam: Checked for restricted content by Gordano's AntiSpam Software
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200907/11
X-Sequence-Number: 32752

Jasmin Dizdarevic wrote:
>  hi,
>  
> i have to create a aggregate function which evaluates a maximum text 
> value but with some conditions i have to take care of.
> is there a way to access a value set of each group?
> e.g.:
>  
> customer ; seg
> 111 ; L1
> 111 ; L2
> 111 ; L1
> 222 ; L3
> 222 ; L3
> 222 ; L2
>  
> the result should look like this:
>  
> 111: L1 - because L1 is higher than L2 and ratio of L1 : L2 is 2 : 1
> 222: L3 - because L3 is higher than L2 and ratio of L3 : L2 is 2 : 1
>  
> i hope you know what i mean.
>  
> ty
>  
You don't have to create an aggregate function.  I have similar problem 
where the part numbers have to order based on the contents and the first 
thing you have to do is split it apart then set the ordering you want.  
This gives you an idea of what you can do and what the results look like. 

If the data in the table is laid out as you describe with where 111 and 
L1  are different fields its very easy and you don't have to create an 
aggregate function  

Select '111', 'L1',
    regexp_replace( 'L1', '[1-9 `]+', ''),
    regexp_replace( 'L1', '[a-zA-Z `]+', '')::integer
union
Select '111', 'L3',
    regexp_replace( 'L3', '[1-9 `]+', ''),
    regexp_replace( 'L3', '[a-zA-Z `]+', '')::integer
union
Select'111', 'L2',
    regexp_replace( 'L2', '[1-9 `]+', ''),
    regexp_replace( 'L2', '[a-zA-Z `]+', '')::integer

order by 3, 4


if the data is 111;L1 in a single field its still very easy. Example 
like so

Select split_part('111;L1', ';',1),
    split_part('111;L1', ';',2),
    regexp_replace( split_part('111;L1', ';',2), '[1-9 `]+', ''),
    regexp_replace( split_part('111;L1', ';',2), '[a-zA-Z `]+', '')::integer
union
Select split_part('111;L3', ';',1),
    split_part('111;L3', ';',2),
    regexp_replace( split_part('111;L3', ';',2), '[1-9 `]+', ''),
    regexp_replace( split_part('111;L3', ';',2), '[a-zA-Z `]+', '')::integer
union
Select split_part('111;L2', ';',1),
    split_part('111;L2', ';',2),
    regexp_replace( split_part('111;L2', ';',2), '[1-9 `]+', ''),
    regexp_replace( split_part('111;L2', ';',2), '[a-zA-Z `]+', 
'')::integer

order by 3, 4 desc