CODEWITHSUNDEEP
sqloracle-database
SMORF
SMORF

Reputation: 499

SQL: Removing duplicates from column

I have been searching for a way to remove duplicates from a specific column. I've found several solutions to remove duplicate rows but, my report has unique entries in some of columns.

select li.catnr, li.av_part_no, li.artist, li.title, li.i_status, bom.av_part_no comp_part_AW, nr.c_catnr MF_CATNR, nr.c_prodtyp MF_PROD, nr1.c_catnr LF_CATNR, nr1.c_prodtyp LF_PROD 
from leos_item li, TABLE(leos_flatbom_pkg.GetFlatBOM(li.av_part_no)) bom, scm_prodtyp sp,
oes_nrbom nr, scm_prodtyp sp1, oes_nrbom nr1, scm_prodtyp sp2
where li.cunr = 'W31836'
and li.item_type = 'FP'
and not li.av_part_no is null
and not li.packtyp = 'PRSA'
and bom.parent_part_no = li.av_part_no
and bom.prodtyp = sp.prodtyp
and sp.pr_typ = 'AW'
and li.catnr = nr.p_catnr
and li.prodtyp = nr.p_prodtyp
and li.packtyp = nr.p_packtyp
and nr.c_prodtyp = sp1.prodtyp
and sp1.pr_typ = 'MT'
and li.catnr = nr1.p_catnr
and li.prodtyp = nr1.p_prodtyp
and li.packtyp = nr1.p_packtyp
and nr1.c_prodtyp = sp2.prodtyp
and sp2.pr_typ = 'LF'
and li.av_part_no = 'A0102449353-CD12-1656'

The script above gives me the following result ...

enter image description here ... what I would like to do is remove the duplicates from columns; MF_CATNR, MF_PROD, LF_CATNR, LF_PROD

Desired result would be ...

enter image description here Is this possible ?

Thanks SMORF

Upvotes: 0

Views: 123

Answers (1)

Dave Sexton
Dave Sexton

Reputation: 11188

Use a combination of CASE and RANK to identify which is the first element that needs to display a result. I am not sure of you data so this is a bit of a guess, but for example your comp_part_AW column would become this:

CASE RANK() OVER (PARTITION BY li.catnr, li.av_part_no, li.artist, li.title, li.i_status
             ORDER BY bom.av_part_no)
  WHEN 1 THEN nr.c_catnr END AS comp_part_AW

Upvotes: 2

Related Questions