Why is dense_rank() function assigning same rank to different records?

Question

I have a table TAB whose structure is like:

create table TAB (
    TRAIN_NUMBER varchar2(5),
    TRAIN_START_DATE date,
    EVENT_CODE varchar2(2),
   INTERCHANGE_FLAG number
)

I have inserted 2 records in it

Insert into TAB Values('12987','04-Nov-2019','HO',1);
Insert into TAB Values('12987','04-Nov-2019','TO',1);

I want only one row in output for which rank is 1, so I have partitioned the data on basis of Train No and TRAIN_START_DATE but I am unable to get the fact that how can two different records get same ranks in case of DENSE_RANK()?

SELECT TRAIN_NUMBER, TRAIN_START_DATE , EVENT_CODE 
FROM (
  SELECT TRAIN_NUMBER, TRAIN_START_DATE, EVENT_CODE, 
         DENSE_RANK() OVER (PARTITION  BY TRAIN_NUMBER, TRAIN_START_DATE ORDER BY INTERCHANGE_FLAG) my_rank 
  FROM TAB
) 
WHERE  my_rank = 1;

I am getting Output as

TRAIN_NUMBER    TRAIN_START_DATE    EVENT_CODE
  12987           04-NOV-19           HO
  12987           04-NOV-19           TO

I want only one row which has my_rank=1 and for that I have used DENSE_RANK().

What should I apply in the query so that I get only one record?

Answer 1

Dense_rank and rank will return the same number as long as the value in it's order by clause remains the same.

The difference between dense_rank and rank is that once the value in the order by clause changes, dense_rank will return the next consecutive number, while rank will return a number that's based on the row number.

Row_number will return a different number for each row in the partition, regardless of the uniqueness of the order by column within the partition. If the order by values aren't unique, row_number will return an arbitrary number.

See a live demo on SQL Fiddle.