Обсуждение: Ordering problem with varchar (DESC) - from general ml.

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

"Alexandre Leclerc" <alexandre.leclerc@gmail.com> writes:
> 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;

What makes you think that query is using an index?  It's probably just
returning the data in physical order, which might look correctly ordered
if you inserted all the data in time order to start with.

> SELECT * from t1 ORDER BY date, time DESC;

Perhaps you mean "ORDER BY date DESC, time DESC" ?

I don't actually see how a varchar field set up like that would sort
in a sensible numeric order at all --- text comparisons are unlikely
to do what you'd like with the optional leading one.  Consider replacing
the varchar with a field of type TIME.

            regards, tom lane