ORDER BY items must appear in the select list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ORDER BY items must appear in the select list

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-01-28 : 13:48:14

I am trying to use the following query, whe i add logtype and sequenceno in the order by clause i am getting this error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Can you please tell me how can i use the logtype andsequenceno both fields in the order by clase.

Here is my query which is used in the sp, this is a dynalic sql string query invloved with almost 9 modules, here i am prsenting query with one moduletype:


SELECT DISTINCT  A.RMID, (Select englishtext from tab_ccsnetpicklists where Pickid = A.LogTypeID and fieldlabelkey='lblLogType')+ '-' + convert(nvarchar(50), A.SequenceNo) + '-' +  A.RMTitle as RMTitle FROM  TAB_ccsNetRM  AS E, TAB_ccsNetRM  AS A INNER JOIN TAB_ccsNetPrograms AS B ON A.ProgID = B.ProgID 
    INNER JOIN TAB_ccsNetProjects AS C ON A.ProjID = C.ProjID
    INNER JOIN TAB_ccsNetContracts AS D ON A.ContractID = D.ContractID 
 WHERE A.Deleted = 0 AND B.Deleted = 0 AND C.Deleted = 0 AND D.DELETED = 0  
            AND A.ProgID = 31 AND A.ProjID = 54 AND A.ContractID = 92 AND A.rmID <> 3570 AND 3570 NOT IN (SELECT ModuleRecordID FROM TAB_ccsNetModuleLinks 
          WHERE ModuleName = 'rm' AND  LinkModuleName = 'rm' AND LinkModuleRecordID = A.rmID) AND 3570 NOT IN (SELECT LinkModuleRecordID FROM TAB_ccsNetModuleLinks 
              WHERE LinkModuleName = 'rm' AND ModuleRecordID = A.rmID AND  ModuleName = 'rm')
  ORDER BY  A.LogTypeID, A.SequenceNo, A.RMID

Thank you very much for the helpful information.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-01-28 : 13:54:14

I added those two fields which are in order by clause to first select statement it is working now.

Thank you.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-28 : 13:55:47

You can not force an order outside of your subquery, you are going to have to find a way to relate RMID to those other fields, or pull out the sub query and turn it into a dervived table and then order on the fields within it.

Edit: Looks like you got your answer :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources