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);

What does your new SQL look like? Can you show us? – CResults Mar 17 '10 at 18:51 — CResults, Mar 17 '10 at 18:51

Peter Lang · Accepted Answer · 2010-07-21T06:00:41.843

2

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

edited Jul 21 '10 at 06:00

answered Mar 17 '10 at 18:30

Peter Lang

Does it work when you execute it directly, using `SQLite Manager` for example? – Peter Lang Mar 17 '10 at 18:43
Interesting - using the manager with both of your examples gives me the same result as the basic "Select thecolumn From your_table". Strange, something must be messed up. – T.T.T. Mar 17 '10 at 19:06
And it works without the `CAST` (except that it is not sorted by numbers)? – Peter Lang Mar 17 '10 at 19:13
I added another way - casting in the sub-query. Good luck :) – Peter Lang Mar 17 '10 at 19:23

score 0 · Answer 2 · edited Aug 18 '17 at 18:37

0

This is way super late, but the order by has to match the select list exactly so:

select distinct cast(column as int)
from table
order by cast(column as int)

edited Aug 18 '17 at 18:37

sds

answered Aug 18 '17 at 18:24

anon

2 Answers2