Select query order by sequenceno

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)

Select query order by sequenceno

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-02-09 : 12:24:25

I have the following query via SP:, how to order by sequence no. i ma using group by is there a way to do order by sequence no:


select 	RM.RMID, RM.rmtitle, 	
CONVERT(varchar(10), RM.DocumentDate, 101) AS DocumentDate, 	
CONVERT(varchar(10), RM.ReceivedDate, 101) AS ReceivedDate,  	
RM.origorganization, RM.origsender, 	
RM.barcode, 	
rtrim(isnull(Stype.EnglishDescription,'''')) as SubjectCode,  	
rtrim(isnull(Ltype.englishtext,'''')) as LogType, 	RM.sequenceno,
rtrim(isnull(PG.ProgNO,'''')) as ProgNO, 	
rtrim(isnull(PJ.ProjNO,'''')) as ProjNO, 	
rtrim(isnull(CT.ContractNO,'''')) as ContractNO, 	
rtrim(isnull(PG.ProgName,'''')) as ProgName,
rtrim(isnull(PJ.ProjName,'''')) as ProjName,
rtrim(isnull(CT.ContractName,'''')) as ContractName, 	
rtrim(isnull(CT.constructionMgr,'''')) as ProjectManager, 	

(select firstname + ' ' + lastname from tab_ccsnetusers where userid =( select TOP 1  assignedto from tab_ccsnetactions
 where modulerecordid= RM.RMID and completedate is null ORDER BY NEWID())) assignedTo, 
(select TOP 1  actiondescription from
 tab_ccsnetactions where modulerecordid= RM.RMID  and completedate is null ORDER BY NEWID() ) AssignedDesc 

FROM TAB_ccsNetRM AS RM JOIN TAB_UserAccess AS U ON (RM.ProgID = U.ProgID AND RM.ProjID = U.ProjID
 AND RM.ContractID = U.ContractID and u.module ='RM' and  u.userid =334 ) 
INNER JOIN TAB_ccsNetPrograms PG ON RM.ProgID = PG.ProgID
 INNER JOIN TAB_ccsNetProjects PJ ON RM.ProjID = PJ.ProjID
 INNER JOIN TAB_ccsNetContracts CT ON RM.ContractID = CT.ContractID 
left outer JOIN TAB_ccsNetPickLists Stype ON (RM.SubjectID = Stype.PICKID AND Stype.FieldLabelKey = 'lblSubject') 
left outer JOIN TAB_ccsNetPickLists Ltype ON (RM.LogTypeID = Ltype.PICKID AND Ltype.FieldLabelKey = 'lblLogType')    
Where 1=1 and RM.deleted=0 and ct.contractid = 89 and RM.SubjectID <> 0  and RM.LogTypeID = 1

GROUP BY RM.sequenceno, rtrim(isnull(Stype.EnglishDescription,'''')),  
rtrim(isnull(Ltype.englishtext,'''')),		
 	
CONVERT(varchar(10), RM.DocumentDate, 101) , 	
CONVERT(varchar(10), RM.ReceivedDate, 101) ,  	
RM.origorganization, RM.origsender, 	
RM.barcode, 
rtrim(isnull(PG.ProgNO,'''')), 	
rtrim(isnull(PJ.ProjNO,'''')), 	
rtrim(isnull(CT.ContractNO,'''')), 	
rtrim(isnull(PG.ProgName,'''')) , 	
rtrim(isnull(PJ.ProjName,'''')) , 	
rtrim(isnull(CT.ContractName,'''')) ,
RM.RMID,  
RM.rmtitle,   	
rtrim(isnull(CT.constructionMgr,''''))

Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 12:27:54

yup just add

ORDER BY RM.sequenceno

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-02-09 : 12:30:32

Hello Visakh,

It is saying incorrect syntax order when i use order by.

I already have group by on top, how to include order by clause?

Thank you very much Visakh.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-02-09 : 12:33:34

Visakh,

Sorry, i got it. it is working now.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 12:37:42

cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources