Re: Ordering problem with varchar (DESC)

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