[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Ordering problem with varchar (DESC)
From:       Alexandre Leclerc <aleclerc () ipso ! ca>
Date:       2007-01-31 18:41:45
Message-ID: 45C0E2E9.5030203 () ipso ! ca
[Download RAW message or body]

Daniel Verite a écrit :
> 	Alexandre Leclerc wrote:
> 
>> 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?
> 
> I believe ORDER BY date, replace(time,'h',':')::time DESC would work.

That worked perfectly. Unfortunately I can't control the sql query in
the situation I am in. But... I know this is the white space that does
the issue.

> Or just use directly a time datatype instead of varchar, or only one datetime
> column instead of the two, and order by that column.
> 
> Or use a leading '0' instead of a leading space when the hour is less than 10...

Yep, this is the only solution that will work for that situation right
now: inserting a leading '0' instead of a white space.

Thank you for your help.
Best regards.

-- 
Alexandre Leclerc

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
[prev in list] [next in list] [prev in thread] [next in thread]