troubleshooting Question

query error modification

Avatar of praveen1981
praveen1981Flag for India asked on
Microsoft SQL Server 2008
3 Comments1 Solution268 ViewsLast Modified:
Hi

I am using follwoing query

SELECT * FROM (
	SELECT ts.Ticket_Id,ts.[description],tas.Comment,tas.Comment_Author_Id,ts.TicketStatus,      
		tas.Alchemy_Status_Name,tas.Alchemy_Status_Number,tas.Created_Date,pn.PriorityId
		, row_number() over (partition BY tas.Ticket_Id ORDER BY Id DESC) AS rn
		, avg(Alchemy_Status_Number * 1.0)  over (partition BY tas.Ticket_Id) AS av_score
		, min(Alchemy_Status_Number)  over (partition BY tas.Ticket_Id) AS min_score
	FROM TicketAnalysisStore tas inner join TicketStore ts on tas.Ticket_Id=ts.Ticket_Id
	inner join PriorityNames pn on pn.PriorityName=ts.Priority 
	where ts.TicketStatus='new' OR ts.TicketStatus='open' OR ts.TicketStatus='pending' 
	OR ts.TicketStatus='solved' OR ts.TicketStatus='closed'  order by PriorityId Asc
	)  AS Info                
WHERE rn = 1
;
I am getting following error.

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

But i want to order by PriorityId  for the above query... can you please suggest.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros