Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id A22B7101438B for ; Thu, 1 Mar 2012 13:23:47 -0400 (AST) Received: from smtp4-g21.free.fr ([2a01:e0c:1:1599::13]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S39jC-00050b-NL for pgsql-sql@postgresql.org; Thu, 01 Mar 2012 17:23:46 +0000 Received: from [192.168.0.89] (unknown [78.237.44.8]) by smtp4-g21.free.fr (Postfix) with ESMTP id 175A24C8113 for ; Thu, 1 Mar 2012 18:23:23 +0100 (CET) Message-ID: <4F4FB086.3080002@club-internet.fr> Date: Thu, 01 Mar 2012 18:23:18 +0100 From: "F. BROUARD / SQLpro" Organization: sqlpro.developpez.com User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Natural sort order References: <20111217113306531983.1eb85a86@klingler.net> In-Reply-To: <20111217113306531983.1eb85a86@klingler.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/12 X-Sequence-Number: 36407 The fastest way is to create a ref table with all possible entries,=20 ordered with an additionnal numerical column, indexing it and make a=20 join from your table to this ref table. A + Le 17/12/2011 11:33, Richard Klingler a =E9crit : > Morning... > > What is the fastest way to achieve natural ordering from queries? > > I found a function at: > http://2kan.tumblr.com/post/361326656/postgres-natural-ordering > > But it increases the query time from around 0.4msecs to 74msecs... > Might be not much if occasional queries are made..but I use it for > building > up a hierarchical tree menu in a web application where every msecs > counts (o; > > > cheers > richard > > --=20 Fr=E9d=E9ric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40= 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & M=E9tiers PACA, ISEN Toulon et CESI/EXIA Aix en Provenc= e Audit, conseil, expertise, formation, mod=E9lisation, tuning, optimisatio= n *********************** http://www.sqlspot.com *************************