Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4AKcOA52787 for ; Thu, 10 May 2001 16:38:24 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 54252 for ; Thu, 10 May 2001 13:38:21 -0700 From: "Josh Berkus" Subject: Re: Order by email address To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Thu, 10 May 2001 13:38:21 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200105/79 X-Sequence-Number: 2880 Herve, Etc. Ooops! A couple of misteaks in that last post: > > toto@aol.com > > tutu@aol.com > > toto@be.com > > tutu@be.com > > toto@yahoo.com > > > > Is it possible and how ? > > Given the relational rule of Atomicity (each discrete piece of > information shall have its own column or row), the solution is for > you > to make "e-mail id" and "domain" seperate fields. Then you can sort: > > ORDER BY mailbox, domain I meant: ORDER BY domain, mailbox > > If this is a legacy database, and splitting the field is not an > option > for you due to exisiting applications/policy, then you'll need to > write > a custom sorting function: > > CREATE FUNCTION email_sort (VARCHAR) > RETURNS CHAR(120) AS ' > DECLARE > email_addr ALIAS for $1; > mail_box CHAR(60); > mail_domain CHAR(60); > BEGIN > mail_box := CAST(SUBSTR(email_addr, 1, (STRPOS(email_addr, ''@'', 1) > -1)) AS CHAR(60)); > mail_domain := CAST(SUBSTR(email_addr, (STRPOS(email_addr, ''@'', 1) > + > 1), 60) AS CHAR(60)); > RETURN mail_box || mail_domain; I meant: RETURN mail_domain || mail_box; > END;' > LANGUAGE 'plpgsql'; > > Then: > > SELECT user_id, email, email_sort(email) as sortcol > FROM users > ORDER BY sortcol; > -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco