Oracle row_number greater than and including 1

Question

I have a query that uses the row_number function. I want to return all rows greater than 1 but also include 1. When I try

row_number()over(partition by .. order by)rn
...rn > 1

I get rn = 2, 3, 4...

If I do

rn >= 1

I get rows that only = 1 along with rows greater than 1.

How do I include 1 in rows greater than 1 but not include the rows that are only 1?

All help is greatly appreciated

Edit

I was afraid my question wouldn't make sense. I am searching items in a storage warehouse and trying to find all locations with the same item. My query is a follows

select * from (
   select item_number, bin_number, qty
    ,row_number()over(partition by item order by loc) rn --maybe I need count(*) over(....)
     from my_table
) where rn >= 1

which returns

item_number | bin_number | qty | rn
12345       |  A1        | 2   | 1
123456      |  A2        | 1   | 1
123456      |  A3        | 1   | 2
123456      |  A4        | 4   | 3

If change

rn > =1

to

rn > 1

I get

item_number | bin_number | qty | rn
123456      |  A3        | 1   | 2
123456      |  A4        | 4   | 3

I want to include all rows when rn is greater than 1

Answer 1

I have worked with an example on the Employees table in the HR schema.

SELECT
    row_number() over (partition by salary order by salary) as serial_no,
    employee_id,
    first_name,
    salary,
    department_id
FROM
    employees
WHERE
    salary IN (
        SELECT
            salary
        FROM
            employees
        GROUP BY
            salary
        HAVING
            COUNT(salary) > 1

    ) ORDER BY salary;

See if this fulfills your purpose and kindly provide a feedback.

Answer 2

Use count instead of row_number.

select * from (select count()over(partition by .. order by) cn
                 from ...) x
where x.cn > 1

If you also want the row_number value, add it as a separate column in the select list.

select * from (select count()over(partition by .. order by) cn,
                      row_number()over(partition by .. order by) rn
                 from ...) x
where x.cn > 1

Updated: Remove the order by from the count(). Here is an example.

create table mytab(id number, name varchar2(100));
insert into mytab values(1,'Ram');
insert into mytab values(1,'Rahim');
insert into mytab values(1,'Reuben');
insert into mytab values(2,'Esther');
insert into mytab values(3,'Veena');
insert into mytab values(3,'Visu');
insert into mytab values(4,'Vishnu');
commit;

select * from mytab;

Output:

ID NAME 
1 Ram 
1 Rahim 
1 Reuben 
2 Esther 
3 Veena 
3 Visu 
4 Vishnu

select * 
  from (select id, 
               name,
               count(1) over(partition by id) cn, 
               row_number() over(partition by id order by name) rn 
          from mytab) x
 where x.cn > 1;

Output:

ID  NAME    CN  RN
1   Rahim   3   1
1   Ram     3   2
1   Reuben  3   3
3   Veena   2   1
3   Visu    2   2