Question on rank over partition [message #673759] |
Sun, 09 December 2018 05:25 |
|
fonzi
Messages: 7 Registered: December 2018
|
Junior Member |
|
|
I have an Oracle table called YEAR_END with the following entries:
ID - PART_NO - DATE
1 1012558 03-NOV-14
2 1012559 03-NOV-14
3 1012560 20-OCT-14
4 1012561 03-NOV-14
5 1012963 03-NOV-14
6 1012564 03-NOV-14
7 1012964 20-OCT-14
8 1012565 03-NOV-14
9 1012566 03-NOV-14
10 1012967 03-NOV-14
11 1012566 20-OCT-14
12 1012565 20-OCT-14
As you can see the part numbers 1012564,1012565 and 1012566 appear twice (but with different dates). The most recent date should have rank number 1 and the second date should have rank number 2. All other parts should have rank number 1 because they only appear once.
The sql I am using is:
SELECT PART_NO, PART_DATE,
RANK()
OVER (partition by PART_NO, PART_DATE ORDER BY PART_NO, PART_DATE desc
) as RNK
from YEAR_END)
The sql should gives me the result below.
ID - PART_NO - DATE - RNK
1 1012558 03-NOV-14 - 1 (RANK)
2 1012559 03-NOV-14 -1 (RANK)
3 1012560 20-OCT-14 - 1 (RANK)
4 1012561 03-NOV-14 - 1 (RANK)
5 1012963 03-NOV-14 - 1 (RANK)
6 1012564 03-NOV-14 - 2 (RANK)
7 1012964 20-OCT-14 - 1 (RANK)
8 1012565 03-NOV-14 - 2 (RANK)
9 1012566 03-NOV-14 - 2 (RANK)
10 1012967 03-NOV-14 - 1 (RANK)
11 1012566 20-OCT-14 - 1 (RANK)
12 1012565 20-OCT-14 - 1 (RANK)
However it is displaying all the parts with rank number 1. If I take out all the entries apart from ID 9 and ID 11 it works (it ranks ID 11 as rank number 1 and ID 9 as rank number 2). But not with all the entries.
Thank you for any replies.
|
|
|
|
|
|
|
|
Re: Question on rank over partition [message #673775 is a reply to message #673764] |
Mon, 10 December 2018 03:51 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The partition clause identifies a set of records to be treated together. Order by defines the order within that set.
Two rows with the same values for the partition columns belong together and two rows with different values don't.
So for the rank functions order by defines the rank order for a set of rows defined by partition by.
It never makes sense to have a column in both partition and order by of an analytic function.
|
|
|
|
Re: Question on rank over partition [message #673779 is a reply to message #673778] |
Mon, 10 December 2018 04:55 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The select can't possibly know how the data was inserted.
So if the method of insertion gives different results then that means either:
a) the different insertion methods put different data in the table
b) your order by isn't deterministic and so the answer can change depended on what order oracle reads the rows from the table.
|
|
|
|
|
|
Re: Question on rank over partition [message #673784 is a reply to message #673780] |
Mon, 10 December 2018 05:29 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
First off - rows in a table are not in any inherent order and you should never write code that relies on them being in an inherent order.
So the fact that a simple select (presumably select * from <table>;) gives data in a different order is meaningless.
Secondly - if you are partitioning by part_no and ordering by part date - then you should get the same rank regardless of the order oracle decides to get the rows from the underlying table.
Copy and paste a sqlplus session showing:
select * from <table>
select PART_NO, PART_DATE,
DENSE_RANK ()
OVER (partition by PART_NO ORDER BY PART_DATE ) as RNK from <table>
For both tables.
|
|
|
Re: Question on rank over partition [message #673786 is a reply to message #673784] |
Mon, 10 December 2018 05:42 |
|
fonzi
Messages: 7 Registered: December 2018
|
Junior Member |
|
|
Quote:
SELECT PART_NO, PART_DATE,
DENSE_RANK ()
OVER (partition by PART_NO ORDER BY PART_DATE ) as RNK from YEAR_END1;
PART_NO PART_DATE RNK
1012558 03-NOV-14 1
1012559 03-NOV-14 1
1012560 20-OCT-14 1
1012561 03-NOV-14 1
1012563 03-NOV-14 1
1012564 20-OCT-14 1
1012564 03-NOV-14 2
1012565 20-OCT-14 1
1012565 03-NOV-14 2
1012566 20-OCT-14 1
1012566 03-NOV-14 2
1012567 03-NOV-14 1
_________________________
SELECT PART_NO, PART_DATE,
DENSE_RANK ()
OVER (partition by PART_NO ORDER BY PART_DATE ) as RNK from YEAR_END2;
PART_NO PART_DATE RNK
1012558 03-NOV-14 1
1012559 03-NOV-14 1
1012560 20-OCT-14 1
1012561 03-NOV-14 1
1012564 03-NOV-14 1
1012565 03-NOV-14 1
1012566 03-NOV-14 1
1012963 03-NOV-14 1
1012964 20-OCT-14 1
1012965 20-OCT-14 1
1012966 20-OCT-14 1
1012967 03-NOV-14 1
[Updated on: Mon, 10 December 2018 05:51] Report message to a moderator
|
|
|
Re: Question on rank over partition [message #673788 is a reply to message #673786] |
Mon, 10 December 2018 05:50 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
And you must understand that we can't see your screen. So if you've got time constraints it's more important that you post what we ask for, not less. Then we can see for ourselves what's going on and hopefully help you solve the issue in time.
EDIT: this post made more sense before the OP changed the previous post.
[Updated on: Mon, 10 December 2018 06:09] Report message to a moderator
|
|
|
Re: Question on rank over partition [message #673790 is a reply to message #673788] |
Mon, 10 December 2018 06:09 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You haven't read your data very carefully.
In year_end1 there are 3 part_nos that appear twice (and so have a row with rnk = 2): 1012564, 1012565 and 1012566.
In year_end2 every part_no appears only once. - check for yourself - they're in numeric order.
The difference in the data isn't just the order of the last two rows.
|
|
|
|