query error modification

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
;

Open in new window

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.
praveen1981Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
use this
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
	   ,ROW_NUMBER() OVER (ORDER BY PriorityId ASC) AS RN
        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' 
      )  AS Info                
WHERE rn = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kent DyerIT Security Analyst SeniorCommented:
First thing, when posting SQL Statements, use the code option from the Post window..

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  

Open in new window

Is there a reason you are doing a SELECT * FROM (SELECT..)?  Are you doing a PIVOT?  I do see you are working with a Partition..  And yes, I see that the sub-SELECT has an alias placed on it.  But, let's break this out first and get it working first.

You should still be able to just SELECT ts.ticket_id, ,,,

You can simplify:
        WHERE  ts.ticketstatus = 'new'
                OR ts.ticketstatus = 'open'
                OR ts.ticketstatus = 'pending'
                OR ts.ticketstatus = 'solved'
                OR ts.ticketstatus = 'closed'

Open in new window


To be just:
        WHERE  ts.ticketstatus IN ('new','open','pending','solved','closed')

Open in new window


Or, if my suspicions are right, you can simplify it even further:
        WHERE  ts.ticketstatus <> 'cancelled'

Open in new window


Your ORDER  BY priorityid ASC can be just ORDER  BY priorityid ..  The system should automatically use ASC by default.

HTH,

Kent
0
praveen1981Author Commented:
Many thanks..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.