CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-groupwindow-functions
Jude92
Jude92

Reputation: 169

Rank Over Partition By in Oracle SQL (Oracle 11g)

I have 4 columns in a table

enter image description here

I just want to return one record based on the maximum Part Receipt Date which would be the first row in the table (The one with Part Receipt date 03/31/2015).

I tried

RANK() OVER (PARTITION BY Company Part Number,Manufacturer Part Number 
            ORDER BY Part Receipt Date DESC,Order Number DESC) = 1

at the end of the WHERE statement and this did not work.

Upvotes: 5

Views: 54576

Answers (2)

user5683823
user5683823

Reputation:

Analytic functions like rank() are available in the SELECT clause, they can't be invoked directly in a WHERE clause. To use rank() the way you want it, you must declare it in a subquery and then use it in the WHERE clause in the outer query. Something like this:

select company_part_number, manufacturer_part_number, order_number, part_receipt_date
from   ( select t.*, rank() over (partition by...  order by...) as rnk
         from   your_table t
       )
where  rnk = 1

Note also that you can't have a column name like company part number (with spaces in it) - at least not unless they are enclosed in double-quotes, which is a very poor practice, best avoided.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271023

This would seem to do what you want:

select t.*
from (select t.*
      from t
      order by partreceiptdate desc
     ) t
where rownum = 1;

Upvotes: 5

Related Questions