SQL Varchar Order

Question

Why when I have records like (ratings = varchar):

A - 100
B - 70
C - 30

Then:

SELECT * FROM `videos` ORDER BY `rating` DESC

Gives:

A - 30
B - 70
C - 100

And:

SELECT * FROM `videos` ORDER BY `rating` ASC

Gives:

A - 100
B - 70
C - 30

But when the (ratings = tinyint):

SELECT * FROM `videos` ORDER BY `rating` DESC

Gives:

A - 100
B - 70
C - 30

Answer 1

When you have a varchar column, the length of the text data and the fact the char '0' comes before char '1' (rather than after char '9') affects the sort order.

e.g. compare

select '100' as col1 
union all 
select '70' as col1
order by col1 asc

with

select '100' as col1 
union all 
select '070' as col1
order by col1 asc

You can get around this by suitably padding all strings to the same length.

Answer 2

you can try:

SELECT * FROM videos ORDER BY rating, videos DESC