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.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
praveen1981

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
tigin44

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
ASKER
praveen1981

Many thanks..
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck