Received: from nezu.gs.verio.net (nezu.gs.verio.net [204.27.65.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4BEZpA00732 for ; Fri, 11 May 2001 10:35:52 -0400 (EDT) (envelope-from jeckermann@verio.net) Received: by NEZU with Internet Mail Service (5.5.2650.21) id <2XWDMQN4>; Fri, 11 May 2001 09:35:40 -0500 Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094A79@NEZU> From: Jeff Eckermann To: "'Frank Bax'" , pgsql-sql@postgresql.org Subject: RE: Order by email address by domain ? Date: Fri, 11 May 2001 09:35:39 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id f4BEZrA00742 X-Archive-Number: 200105/97 X-Sequence-Number: 2909 ??? I don't think anyone suggested adding a new builtin function. Yes, your suggestion is good for an occasional use. Where this kind of functionality is likely to be needed on a continuing basis, my experience is that wrapping the code up in a custom function is easier and cleaner than writing it out every time. Saves a lot of typing, not to mention the risk of typos (which could give spurious results without being obvious about it). Also, a function allows for indexing on that value, which can be a great aid to performance. I have found that sometimes it is better to just add a column or two to the table to contain the needed key, because with large amounts of data that can be much quicker. Doing this too much though can lead to a cluttered database, and a loss of clarity about just what all of those extra fields are for... It's a judgement call. Just my $0.01 (That's $0.02 Australian :-)) > -----Original Message----- > From: Frank Bax [SMTP:fbax@sympatico.ca] > Sent: Thursday, May 10, 2001 8:53 PM > To: pgsql-sql@postgresql.org > Cc: herve@elma.fr > Subject: Re: Order by email address by domain ? > > Why is everyone else suggesting new functions? This works (in 6.5.3): > > ORDER BY lower(substring(email from position('@' in email)+1 )), > lower(email) > > remove the lower() functions if you don't need them (I had mixed case > addresses). > > I am guessing/assuming that it's cheaper to just use entire email address > in second key rather than extract before the '@' character. > > Frank > > At 08:37 PM 5/10/01 +0200, you wrote: > >Hi, > > > >I just want to order by a listing of email address by domain like : > > > >toto@aol.com > >tutu@aol.com > >toto@be.com > >tutu@be.com > >toto@yahoo.com > > > >Is it possible and how ? > > > >Thanks ! > >-- > >Hervé Piedvache > > > >Elma Ingenierie Informatique > >6, rue du Faubourg Saint-Honoré > >F-75008 - Paris - France > >http://www.elma.fr > >Tel: +33-1-44949901 > >Fax: +33-1-44949902 > >Email: herve@elma.fr > > > >---------------------------(end of broadcast)--------------------------- > >TIP 6: Have you searched our list archives? > > > >http://www.postgresql.org/search.mpl > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html