SQL ORDER BY clause ERROR in the ROW_NUMBER

I want to use ROW_NUMBER() function and get first and latest values.
I write bellow query. But I got an error.

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

help me to solve the issue. Below the sql query

SELECT *
FROM(
SELECT OPP_ID,PRJ_ID,
ROW_NUMBER() OVER (PARTITION BY OPP_ID ORDER BY MAX(CREATION_DATE) DESC) AS RN
FROM OPPOR
GROUP BY OPP_ID,PRJ_ID
ORDER BY MAX(CREATION_DATE) DESC) OP
WHERE OP.RN = 1


1 Answer
1

The row_number function can do it's own aggregation and ordering, so no need to use group by or order by in your subquery (order by won't work in subqueries as you've seen). It is a little unclear if you want to partition by opp_id or opp_id and prj_id though. But this should be what you're looking for:

row_number

group by

order by

order by

partition by

SELECT *
FROM(
SELECT OPP_ID,PRJ_ID,
ROW_NUMBER() OVER (PARTITION BY OPP_ID ORDER BY CREATION_DATE DESC) AS RN
FROM OPPOR
) OP
WHERE OP.RN = 1


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.