Sort on string representation of integer?

Question

I have a bookmark table with a bookmark type field which has type int(11).

To sort my bookmarks by type i can execute a simple query:

select * from bookmarks order by type

Works great.

Except this sorts bookmarks by integer type.

In my gui i convert the integer type to a string representation:

1=Great
2=Bad
3=Wow
...

The above query results in:

bookmarks of type Great
bookmarks of type Bad
bookmarks of type Wow

because the bookmarks are sorted on integer type.

I would like to get the result:

bookmarks of type Bad
bookmarks of type Great
bookmarks of type Wow

How can i sort on the string representation of the integer type without changing the field type and not losing handy query features like pagination?

Why don't you just rearrange the numbers with respect to their string values? `1=Bad`, `2=Great`, and `3=Wow`. — , Jun 08 '18 at 11:56
You could make type(int) an fk to a table of [type | description] order by the former, select the latter. — Alex K., Jun 08 '18 at 11:56

score 1 · Accepted Answer · answered Jun 08 '18 at 11:58

1

You can transform your order by to execute on the string not on the id:

select * from bookmarks
order by CASE
WHEN type = 1 THEN 'Great'
WHEN type = 2 THEN 'Bad'
WHEN type = 3 THEN 'Wow'
ELSE 'Zzz' END

answered Jun 08 '18 at 11:58

A. Colonna

Cheers, i gave it a shot and it works. I like this solution cause it doesn't require any database changes and very few changes in my code. – Tinus Tate Jun 08 '18 at 12:12

score 1 · Answer 2 · answered Jun 08 '18 at 12:05

The formal way will be to create a new types table that will be looks like this one:

types

id    type
1     Greate
2     Bad
3     Wow

Then inside the query you can do something like this:

Select *
From bookmarks inner join types
On bookmarks.type = types.id
Order By types.type

2 Answers2