Home » SQL & PL/SQL » SQL & PL/SQL » Question on rank over partition (Oracle 11g)
Question on rank over partition [message #673759] Sun, 09 December 2018 05:25 Go to next message
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 #673760 is a reply to message #673759] Sun, 09 December 2018 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Remove PART_DATE from "partition by" clause and use dense_rank instead of rank.

Re: Question on rank over partition [message #673761 is a reply to message #673760] Sun, 09 December 2018 07:30 Go to previous messageGo to next message
fonzi
Messages: 7
Registered: December 2018
Junior Member
Thank you for your reply and apologies for my errors. I wasn't sure of the exact version of Oracle as it is an issue at work and I won't have access to that environment until tomorrow.
I won't be able to test your solution until then either unless I get access to an oracle sandbox online before then.
Thank you again.
Re: Question on rank over partition [message #673762 is a reply to message #673761] Sun, 09 December 2018 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also remove PART_NO from "order by" as you partition by PART_NO all rows in a partition have the same PART_NO.

Re: Question on rank over partition [message #673763 is a reply to message #673762] Sun, 09 December 2018 12:53 Go to previous messageGo to next message
fonzi
Messages: 7
Registered: December 2018
Junior Member
This works, thank you


SELECT PART_NO, PART_DATE,
DENSE_RANK () 
OVER (partition by      PART_NO       ORDER BY PART_DATE    ) as  RNK from YEAR_END;

Re: Question on rank over partition [message #673764 is a reply to message #673763] Sun, 09 December 2018 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thank you for the feedback.

Re: Question on rank over partition [message #673775 is a reply to message #673764] Mon, 10 December 2018 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 13554
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 #673778 is a reply to message #673775] Mon, 10 December 2018 04:47 Go to previous messageGo to next message
fonzi
Messages: 7
Registered: December 2018
Junior Member
The solution works but only if I insert the rows to the table one by one by performing the following steps:

1. Paste the following code
Insert into YEAR_END (PART_NO,PART_DATE,) values ('1012558','03-NOV-14');
2. Execute line
3. Modify values in step 1 to next entry and go to step 2 until all twelve lines are entered

If I insert all twelve entries at once in a script the solution returns number 1 for every rank.
I have added TRIM() to the solution but it makes no difference.
Mad
Re: Question on rank over partition [message #673779 is a reply to message #673778] Mon, 10 December 2018 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 13554
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 #673780 is a reply to message #673779] Mon, 10 December 2018 05:19 Go to previous messageGo to next message
fonzi
Messages: 7
Registered: December 2018
Junior Member
I created two different tables ("year_end_2" and "year_end_1") and inserted the exact same rows in the exact same order except for the year_end_2 table the insert was done with a bulk insert and for the year_end_1 table the insert was done with an insert one by one.
The year_end_2 table gave a rank of 1 for every row. The year_end_1 table gave the correct rank.

If I do a basic select from for each table the last two rows are in different order in the tables (see below). That is the only difference.

year_end_1 (correct)

PART_NO PART_DATE
1012558 03-Nov-14
1012559 03-Nov-14
1012560 20-Oct-14
1012561 03-Nov-14
1012564 03-Nov-14
1012565 03-Nov-14
1012566 03-Nov-14
1012563 03-Nov-14
1012564 20-Oct-14
1012565 20-Oct-14
1012567 03-Nov-14
1012566 20-Oct-14

year_end_2 (incorrect)

PART_NO PART_DATE
1012558 03-Nov-14
1012559 03-Nov-14
1012560 20-Oct-14
1012561 03-Nov-14
1012564 03-Nov-14
1012565 03-Nov-14
1012566 03-Nov-14
1012963 03-Nov-14
1012964 20-Oct-14
1012965 20-Oct-14
1012966 20-Oct-14
1012967 03-Nov-14
Re: Question on rank over partition [message #673782 is a reply to message #673779] Mon, 10 December 2018 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"'03-NOV-14'" this is not a date but a string which may or not represent a valid date:
SQL> select to_date('03-NOV-14') from dual;
select to_date('03-NOV-14') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


Once again
Quote:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: Question on rank over partition [message #673783 is a reply to message #673780] Mon, 10 December 2018 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I created two different tables ("year_end_2" and "year_end_1") and inserted the exact same rows in the exact same order except for the year_end_2 table the insert was done with a bulk insert and for the year_end_1 table the insert was done with an insert one by one.
The year_end_2 table gave a rank of 1 for every row. The year_end_1 table gave the correct rank.

How can we reproduce what you say?
In addition, don't tell SHOW us, use SQL*Plus and copy and paste your session, the WHOLE session.

Re: Question on rank over partition [message #673784 is a reply to message #673780] Mon, 10 December 2018 05:29 Go to previous messageGo to next message
cookiemonster
Messages: 13554
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13554
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 Go to previous messageGo to next message
cookiemonster
Messages: 13554
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.
Re: Question on rank over partition [message #673791 is a reply to message #673790] Mon, 10 December 2018 06:27 Go to previous message
fonzi
Messages: 7
Registered: December 2018
Junior Member
Thank you cookiemonster, silly of me, I am a student on work experience, I don't really like sql to be honest, I get stressed by it.
Thanks again.
Previous Topic: Problem with the product user profile
Next Topic: Trigger Error ORA-00604
Goto Forum:
  


Current Time: Sun Apr 21 03:11:49 CDT 2019