Why doesn't SQL DISTINCT work with ORDER BY CAST?

Question

Including DISTINCT to an SQL query that also uses ORDER BY CAST(thecolumn AS int) as shown here seems to remove that sorting functionality.

Any reason these cant work together? (Using sqlite with the C api)

Thanks.

EDIT:
Started with -

sprintf(sql, "SELECT DISTINCT rowX FROM TableX Order By Cast(rowX As int) LIMIT 150 OFFSET %s;", Offset);

rowX is Type CHAR(5)

NOW:

sprintf(sql, "Select rowX FROM(Select Distinct rowX From TableX)t Order By Cast(rowX As int) LIMIT 150 OFFSET %s;", Offset);

Answer 1

I used the following with sqlite, and sorting worked fine:

Select Distinct thecolumn
From your_table
Order By Cast(thecolumn As int)

---

Have you tried putting the DISTINCT into a sub-query?

Select thecolumn
From
(
  Select Distinct thecolumn
  From your_table
) t
Order By Cast(thecolumn As int)

I would expect it to work that way.

---

One more way:

Select thecolumn
From
(
  Select Distinct Cast(thecolumn As int) As thecolumn
  From your_table
) t
Order By thecolumn