SQL ORDER BY clause ERROR in the ROW_NUMBER

Question

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

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:

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