Thread: Ordering problem with varchar (DESC)

Ordering problem with varchar (DESC)

From
Alexandre Leclerc
Date:
Hi all,

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
Projets spéciaux

Ipso Systèmes Stratégiques inc.
176 boul. Harwood (suite 10), Vaudreuil-Dorion, QC, J7V 1Y2
Tel: 450-424-6847 ext. 108 / 1-800-879-4776
Fax: 450-424-8439
http://www.ipso.ca/