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.
ORDER BY items must appear in the select list
Posted - 2010-01-28 : 13:48:14
