Skip to Main Content
  • Questions

  • select duplicate records based on one column only

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 14, 2018 - 1:15 pm UTC

Last updated: November 28, 2018 - 12:50 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

I have set of data as below,

CASE#           UNIT#
------------------------
X0002270301 
X0002460601 T0707802116
X0002460601 
X0002683101 
X0002683101 T0721128616
X0002684101
X0002922501 
X0005125201 
X0005125201 T0813774816

Need to select only data like ,

CASE#           UNIT#
------------------------
X0002270301 
X0002460601 T0707802116
X0002683101 T0721128616
X0002684101
X0002922501 
X0005125201 T0813774816


wanted to exclude Case#s which are repeated without Unit#s

Please help. Thanks a lot.







and Connor said...

You didn't give us insert statements :-(, so I've not tested this....but this should be close

select *
from 
  ( select t.*,
       row_number() over ( partition by case# order by rowid ) as seq
    from t
  )
where unit# is not null or seq = 1


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Easy

Gh, February 15, 2018 - 7:50 am UTC

Select all group by all

aggregation

Rajeshwaran, Jeyabal, February 15, 2018 - 12:34 pm UTC 

demo@ORA12C> select * from t;

CASE_ID              UNITS
-------------------- --------------------
X0002270301
X0002460601          T0707802116
X0002460601
X0002683101
X0002683101          T0721128616
X0002684101
X0002922501
X0005125201
X0005125201          T0813774816

9 rows selected.

demo@ORA12C> select case_id, max(units)
  2  from t
  3  group by case_id
  4  order by case_id;

CASE_ID              MAX(UNITS)
-------------------- --------------------
X0002270301
X0002460601          T0707802116
X0002683101          T0721128616
X0002684101
X0002922501
X0005125201          T0813774816

6 rows selected.

demo@ORA12C>

For rajesh

Gh, February 17, 2018 - 10:56 am UTC

The requirement is to have also a null value for same Id..
So I repeat. Select all cols group by all cols. Whatever u use nvl or not.

Question

Raj, November 27, 2018 - 10:30 am UTC

Hi, I have data as mentioned below.

ID Debit Charges
154330749522 2920 null
154330749522 null 12

I need single row & want to place charges at null in first row. please help.
Connor McDonald
November 28, 2018 - 12:50 am UTC

GOOD_BAD
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.