Jump to content

Order by Column Alias


ShadowMage
 Share

Recommended Posts

I have the following SQL query:

select Project.ProjectID, <<long string of functions to parse state>> as State from Project Project where (Project.Character05 LIKE '%, FL %'  OR Project.Character05 LIKE '%, GA %' OR Project.Character05 LIKE '%, NY %') order by State

The above query fails, stating that there is an unknown column (referring to the alias in the ORDER BY clause). I know it is referring to the alias in the ORDER BY because if I remove the ORDER BY, the query works.If I put backticks around State (...order by `State`) I get an error complaining of a syntax error (no other details, just that there is a syntax error). Single quotes (...order by 'State') runs the query, but doesn't actually sort the results. Double quotes (...order by "State") gives me the same result as no quotes. I have tried each variation of the ORDER BY with and without the corresponding quotes on the original alias as well (ie, ...as `State`, ...as 'State', ...as "State"). It doesn't seem to like the backticks because those give me an error no matter where they are placed (even if I put them by the original alias and remove the ORDER BY). This is running on a Progress database. Can anybody help? How do I get it to sort based on the alias?

Link to comment
Share on other sites

have you tried

select Project.ProjectID, <<long string of functions to parse state>> as 'State' from Project Project where (Project.Character05 LIKE '%, FL %'  OR Project.Character05 LIKE '%, GA %' OR Project.Character05 LIKE '%, NY %') order by State

single quotes around alias 'State' with no quotes around ORDER BY State

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...