A column has been specified more than once in the order by list.

I have a function dbo.FIELD that extracts a part of the value from a field. I want to get two pieces and order by them.

Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.

How can I do this?

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD(WORK_ID, '*', 1) ASC

These work:

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, WORK_ID ASC

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD('WORK_ID', '*', 1) ASC

Check here for responses to your duplicate post in SQL Server Database Engine.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).