• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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.
0
praveen1981
Asked:
praveen1981
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now