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

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

3753 Provider is not specified and there is no designated default provider.

i just bought a brand new server and put windows server 2003 x64 sp1 on it then put sql server 2000 sp4 on that and set up a trusted odbc connection. however, it gave me the following error:
SQLSTATE:IM002, diagnosis:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
so i made a vbs file (adodb.connection) to see if that could connect to it and it came out with this on the err.number err.description
err.number = 3753
err.description = Provider is not specified and there is no designated default provider.
this even happened when i tried to use a dsnless connection to the old server machine so i dont know if its a problem with whatever is trying to use the connection or odbc or what. please can someone tell me what can i do to fix this problem?
i dont know if this is just a missing driver or something, odbc only has "SQL Server" in the list (C:\WINDOWS\System32\SQLSRV32.dll i think) wheras all my other machines have something like 25 or 30 drivers in the list, but "SQL Server" is the one im trying to use anyway..
edit: iis is not installed on this new server. other machines (with iis and vbs) can connect using dsnless or dsn with uid and pwd. also i think asp might have a different err.number for this problem than vbs' 3753

Due to the length of time since this issue was posted, I'm marking it as resolved. If you're still in need of help, please respond here.

Paul

3753 Provider is not specified and there is no designated default provider.

i just bought a brand new server and put windows server 2003 x64 sp1 on it then put sql server 2000 sp4 on that and set up a trusted odbc connection. however, it gave me the following error:
SQLSTATE:IM002, diagnosis:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
so i made a vbs file (adodb.connection) to see if that could connect to it and it came out with this on the err.number err.description
err.number = 3753
err.description = Provider is not specified and there is no designated default provider.
this even happened when i tried to use a dsnless connection to the old server machine so i dont know if its a problem with whatever is trying to use the connection or odbc or what. please can someone tell me what can i do to fix this problem?
i dont know if this is just a missing driver or something, odbc only has "SQL Server" in the list (C:\WINDOWS\System32\SQLSRV32.dll i think) wheras all my other machines have something like 25 or 30 drivers in the list, but "SQL Server" is the one im trying to use anyway..
edit: iis is not installed on this new server. other machines (with iis and vbs) can connect using dsnless or dsn with uid and pwd. also i think asp might have a different err.number for this problem than vbs' 3753

Due to the length of time since this issue was posted, I'm marking it as resolved. If you're still in need of help, please respond here.

Paul