Re: Ordering problem with varchar (DESC)

[Alexandre Leclerc <aleclerc(at)ipso(dot)ca>]

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