Re: Ordering problem with varchar (DESC) - Mailing list pgsql-general

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.

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...

Regards,

--
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org