Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 2D4599FB1DF for ; Wed, 31 Jan 2007 12:32:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 25162-04 for ; Wed, 31 Jan 2007 12:32:15 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.224]) by postgresql.org (Postfix) with ESMTP id F20E39FA315 for ; Wed, 31 Jan 2007 12:32:15 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 68so262196wra for ; Wed, 31 Jan 2007 08:32:15 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=ZGO0WWzDr9N3yfYwamuZKXAuBO6t982eny5Oiprb683YDRKI0J6y5Z82Mg9TVPOMo/4fVsNpOmzBQS49h3NrltRlrketGZv3Z2XjccA7b5yyCQx/NzgOWO8oCO6ie6xiiYggybFRHb662JmzJFFBcs9l6aBlJjZktnRVaIF794k= Received: by 10.78.149.13 with SMTP id w13mr158610hud.1170261134385; Wed, 31 Jan 2007 08:32:14 -0800 (PST) Received: by 10.78.173.4 with HTTP; Wed, 31 Jan 2007 08:32:09 -0800 (PST) Message-ID: <1dc7f0e30701310832r2da685d4ifd858f34373af84a@mail.gmail.com> Date: Wed, 31 Jan 2007 11:32:09 -0500 From: "Alexandre Leclerc" To: pgsql-admin@postgresql.org Subject: Ordering problem with varchar (DESC) - from general ml. MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/297 X-Sequence-Number: 24453 Hi all, I don't want to double post, but I see nothing hapening in the general mailling list, so I post here in case any one has an idea about what is going on. We have a column (varchar) that has plain text time and it is indexed. When I do a query with the index, all the data is in the right order, but when I user ORDER BY .. DESC, the order is messed up. Example: By index 1: (date, time, data) SELECT * from t1; date (date type) time (varchar) data 2007-01-17 8h40 d1 2007-01-30 9h30 d2 2007-01-30 12h00 d3 2007-01-30 13h45 d4 2007-01-30 17h20 d5 SELECT * from t1 ORDER BY date, time DESC; date (date type) time (varchar) data 2007-01-30 9h30 d2 2007-01-30 17h20 d5 2007-01-30 13h45 d4 2007-01-30 12h00 d3 2007-01-17 8h40 d1 I don't know why, this is like if the 'time' varchar was trimmed then used for the ordering. How can I fix that so that the result is exactly like the first one but perfectly reversed in it's order? Best regards. -- Alexandre Leclerc