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

Since you are calling a function, and the function 'could' run two times and return the same information both time, it 'could' create a situation where a ORDER BY column is duplicated.

It seems to me that shouldn't be an issue, but it is... (I don't know why SQL Server should care if my query is 'ORDER BY LastName, LastName')