using Row_Number in a CTE

[azwaan]

I have an order processing system, which needs to provide a customer with his position in the waiting queue for his order.

I tried to use the ROW_NUMBER() function in a CTE (Common table expression) and then select the row pertaining to the Customer. however it says i cannot use a ORDER BY clause in a CTE. this is the kind of query i tried

WITH AVIEW(QUE_POS, CREATED_BY) AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY ORDER_TIME DESC) AS 'QUE_POS',
Q.[CUSTOMER]
FROM [dbo].[ORDER] AS Q
WHERE Q.IS_DELETED = 0
ORDER BY Q.CREATED_TIME
)

SELECT
@QUE_POS = QUE_POS
FROM AVIEW
WHERE CUSTOMER = @USER_ID


any suggestions on how i might be able to achieve this?

 

[r937]

remove the ORDER BY clause

the position is given by the OVER function which has its own ORDER BY

also, please note AVIEW does not have a column called CUSTOMER, so you might get another error on the main query

r937.com | rudy.ca