Link to home
Create AccountLog in
Avatar of praveen1981
praveen1981Flag for India

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Kent Dyer
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
Avatar of praveen1981

ASKER

Many thanks..