Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

partition by query help needed

MBAppsMar 29 2024 — edited Mar 29 2024

Hi Experts

query help needed , requirement is like to pull out those records for an item number starts with DNO* items having serial number, same serial number attached to another item number.

with my query i am getting below records

create table xx_dummy (item_number varchar2(240),serial_number number);

insert into xx_dummy(item_number,serial_number)

values ('DNO220',1234),

('DA0221',1234),

('DNO222',2345),

('RV0223',2345),

('17505A',4567),

('SCR1700',4567);

select * from (select a.item_number,a.serial_number,count(*) over(partition by a.serial_number) counta

from xx_dummy a

group by a.item_number,a.serial_number)

where counta > 1;

item_number | serial_number

DNO220 | 1234

DA0221 | 1234

DNO222 | 2345

RV0223 | 2345

17505A | 4567

SCR1700 | 4567

but the expected output is like

item number | serial number

DNO220 | 1234

DA0221 | 1234

DNO222 | 2345

RV0223 | 2345

Thanks

Comments
Post Details
Added on Mar 29 2024
4 comments
99 views